I am looking at inserting a simple formula that captures prices for the month that is held on the site sheet vertically into a master sheet horizontally.

I want to enter a formula ='Site Name'!$E$6 (for October) and to then drag into November and for the formula to then be ='Site Name'!$E$7, then 8, 9, 10 right up to March.

When I currently do this, the formula changes to ='Site Name'!$D$6. Is there a way to do what I am after?

Site Sheet

Master Sheet

CodePudding user response：

Use INDEX:

=INDEX('Site Name'!$E:$E,COLUMN(A1) 5)

Now as it is dragged to the right it will change the row reference to 1.