Different data sheets - same cell, different sheet
 

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 !

Different data sheets - same cell, different sheet
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"VeDe" wrote in message ...
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 !

Different data sheets - same cell, different sheet
 

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"VeDe" wrote in message
...
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 !