"dragging" formulas to change sheets

[Vostro12]

Hello,

I've a spreadsheet with multiple sheets called "Obsevations (1)", "Observations (2)" ... "Observations (20)".

I need a summary page, and want to copy cell E16 from each of the Observation sheets.

I have tried by going: =Observations (1)!$E$16 and dragging, but the reference to the sheet does not change.

Instead of doing this manually, is there any other way that Excel can automatically update the sheet name/reference?

Thank you very much

 

[MrKowz]

Are you dragging it down the row or across the columns?

 

[repairman615]

Hello
Welcome to the board!

Try:

=INDIRECT("observations"&ROW(A1)&"!"&"$E$16")

 

[repairman615]

Also I just noticed this does not work with sheetnames like "Observation(1)"...It does work with "Observation1".

Ammending to:
=INDIRECT("observations("&ROW(A1)&")!"&"$E$16")
seems to throw off the indirect resulting in a #REF! error.