| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
link data from multiple workheets
I have data stored in multiple worksheets with identical layout (eg reference
no is always in cell B2 for every sheet). I would like to summarise selected data onto a single sheet with the data presented in rows. I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from sheet 3!B2 to sheet 1!B3 etc. I can create link from sheet 2 to sheet 1 but if I try to copy the link formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3. Can I make the link reference jump to the next sheet each time? is this possible? Martin McLaughlin |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
link data from multiple workheets
=INDIRECT("'sheet "&ROW()&"'!B2")
-- David Biddulph "Martin McLaughlin" wrote in message ... I have data stored in multiple worksheets with identical layout (eg reference no is always in cell B2 for every sheet). I would like to summarise selected data onto a single sheet with the data presented in rows. I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from sheet 3!B2 to sheet 1!B3 etc. I can create link from sheet 2 to sheet 1 but if I try to copy the link formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3. Can I make the link reference jump to the next sheet each time? is this possible? Martin McLaughlin |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
link data from multiple workheets
Thanks David
It didn't work initally and I can't quite figure out how it works and the significance of " and ' (it seems wuite sensetive to this. I had never even heard of the INDIRECT commend until yesterdya but I got it working eventually. thanks for your help -- Martin McLaughlin "David Biddulph" wrote: =INDIRECT("'sheet "&ROW()&"'!B2") -- David Biddulph "Martin McLaughlin" wrote in message ... I have data stored in multiple worksheets with identical layout (eg reference no is always in cell B2 for every sheet). I would like to summarise selected data onto a single sheet with the data presented in rows. I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from sheet 3!B2 to sheet 1!B3 etc. I can create link from sheet 2 to sheet 1 but if I try to copy the link formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3. Can I make the link reference jump to the next sheet each time? is this possible? Martin McLaughlin |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
link data from multiple workheets
The double quotes " are the delimiters of a text string.
The single quotes ' are used to surround the name of a worksheet if that name includes spaces. -- David Biddulph "Martin McLaughlin" wrote in message ... Thanks David It didn't work initally and I can't quite figure out how it works and the significance of " and ' (it seems wuite sensetive to this. I had never even heard of the INDIRECT commend until yesterdya but I got it working eventually. thanks for your help -- Martin McLaughlin "David Biddulph" wrote: =INDIRECT("'sheet "&ROW()&"'!B2") -- David Biddulph "Martin McLaughlin" wrote in message ... I have data stored in multiple worksheets with identical layout (eg reference no is always in cell B2 for every sheet). I would like to summarise selected data onto a single sheet with the data presented in rows. I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from sheet 3!B2 to sheet 1!B3 etc. I can create link from sheet 2 to sheet 1 but if I try to copy the link formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3. Can I make the link reference jump to the next sheet each time? is this possible? Martin McLaughlin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Link multiple worksheets while extracting data | Excel Worksheet Functions | |||
| How do I link data between multiple excel spreadsheets | Excel Discussion (Misc queries) | |||
| How do I link lots of data in multiple worksheets | Excel Worksheet Functions | |||
| What formula can i use to populate info from 2 other workheets ? | New Users to Excel | |||
| Link Data from Multiple Worksheets | Excel Worksheet Functions | |||
Linear Mode
