| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Enter data from same cell in different sheets into a new sheet | Excel Worksheet Functions | |||
| How do i collate data from different sheets in one sheet? | Excel Discussion (Misc queries) | |||
| collecting data from many sheets to one sheet | Excel Discussion (Misc queries) | |||
| new sheet with data from other sheets | Excel Discussion (Misc queries) | |||
| copy data from various sheets into one sheet | Excel Worksheet Functions | |||
Linear Mode
