Incrementing referenced cell in cross sheet formula

[barryg80]

Hi there,

Excel 2013.

My spreadsheet has 2 sheets. In sheet B I want to reference cells in sheet A using the ='Sales+Costs'!M16 syntax. So in sheet 2 Cell D77 is going to reference ='Sales+Costs'!M16. Thats fine for one cell but cell D78 then needs to reference ='Sales+Costs'!N16. Then D79 ref ='Sales+Costs'!O16...etc.

How do I do this please without having to manually adjust the forumla as there area few hundred cells involved..? If i copy the cell and paste down it pastes the values by the next column whereas I need to paste them by row.

Thank you

 

[Special-K99]

Try

=OFFSET('Sales+Costs'!$A$1,15,ROW()-65)

 

[barryg80]

Try

=OFFSET('Sales+Costs'!$A$1,15,ROW()-65)

Brilliant that works. Think I understand it apart from the -65 part, can you tell me what's that doing?

Many thanks for this