Different data sheets - same cell, different sheet

[VeDe]

We put together a questionnaire in xls. Got over 100 of them back, and have
now to pull together the info we gathered. I put all 100 in one single
workbook, each time in a different sheet. Would now need to put all the
answers given per question, in a separate sheet. If I put f.i. in a cell
='1'!A37, I get there the answer of sheet one, cell 37. But if I drag this
down to copy this, the sheet always stays the first one, while I would like
xls to change each time the sheet, but keep the cell. So how can I copy this
that I get in the cell below ='2'!A37, next one ='3'!A37 and so on. Can
anyone help me with this ?
Thanks a lot in advance !

 

[Ron de Bruin]

If you have numbers as sheet names you can try this
http://www.rondebruin.nl/linksum.htm

 

[Bernard Liengme]

Rather than ='1'!A37, use =INDIRECT(ROW(A1)&"!A37") or if you want to keep
the single quotes
=INDIRECT("'"&ROW(A1)&"'!A1") (this begins double-quote, single-quote,
double-quote& double-quote single-quote.
This evaluates to to your formula but when you drag it down to the next row
it becomes
=INDIRECT(ROW(A2)&"!A1") which is the same as ='2'!A37

best wishes