Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Relative Sheet Reference (Summary Sheet)
Here is the scenario. There is an Excel Workbook with multiple
worksheets (we're talking 35+ and growing). The client wants to create a summary sheet that lists the key information on the different worksheets. Of course the data on the individual worksheets is in columns and the summary sheet is in rows. I know how to create a 3D cell reference to different cells in a workbook but to do it for all the individual cells is not acceptable. Is there a way to make this formula in a cell: =WorksheetA!B1 And have it copy down so the formula automatically populates with the next worksheets like this? =WorksheetB!B1 =WorksheetC!B1 =WorksheetD!B1 =WorksheetE!B1 I tried the substitute function but I would have to put in the name of each worksheet. I guess what I am trying to ask is if there is a way to make the worksheet references absolute. I did find something called the SheetOffSet feature that works EXCEPT when I copy the formula, it does NOT make the worksheet reference relative (the 2 in the formula below) -- even when I make the cell reference absolute. =SHEETOFFSET(2,B2) http://www.j-walk.com/ss/excel/tips/tip63.htm I would be most grateful for any suggestions/ideas you can toss my way. |
#2
|
|||
|
|||
Maybe combine the sheetoffset function with the row function.
If your summary begins in cell A2: =SHEETOFFSET(2,B2) becomes =SHEETOFFSET(ROW(A2),B2) ROW(A2) evaluates to 2. When you copy this down, A2 becomes A3, A4, etc. " wrote: Here is the scenario. There is an Excel Workbook with multiple worksheets (we're talking 35+ and growing). The client wants to create a summary sheet that lists the key information on the different worksheets. Of course the data on the individual worksheets is in columns and the summary sheet is in rows. I know how to create a 3D cell reference to different cells in a workbook but to do it for all the individual cells is not acceptable. Is there a way to make this formula in a cell: =WorksheetA!B1 And have it copy down so the formula automatically populates with the next worksheets like this? =WorksheetB!B1 =WorksheetC!B1 =WorksheetD!B1 =WorksheetE!B1 I tried the substitute function but I would have to put in the name of each worksheet. I guess what I am trying to ask is if there is a way to make the worksheet references absolute. I did find something called the SheetOffSet feature that works EXCEPT when I copy the formula, it does NOT make the worksheet reference relative (the 2 in the formula below) -- even when I make the cell reference absolute. =SHEETOFFSET(2,B2) http://www.j-walk.com/ss/excel/tips/tip63.htm I would be most grateful for any suggestions/ideas you can toss my way. |
#3
|
|||
|
|||
Hi
Maybe you like this macro http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Here is the scenario. There is an Excel Workbook with multiple worksheets (we're talking 35+ and growing). The client wants to create a summary sheet that lists the key information on the different worksheets. Of course the data on the individual worksheets is in columns and the summary sheet is in rows. I know how to create a 3D cell reference to different cells in a workbook but to do it for all the individual cells is not acceptable. Is there a way to make this formula in a cell: =WorksheetA!B1 And have it copy down so the formula automatically populates with the next worksheets like this? =WorksheetB!B1 =WorksheetC!B1 =WorksheetD!B1 =WorksheetE!B1 I tried the substitute function but I would have to put in the name of each worksheet. I guess what I am trying to ask is if there is a way to make the worksheet references absolute. I did find something called the SheetOffSet feature that works EXCEPT when I copy the formula, it does NOT make the worksheet reference relative (the 2 in the formula below) -- even when I make the cell reference absolute. =SHEETOFFSET(2,B2) http://www.j-walk.com/ss/excel/tips/tip63.htm I would be most grateful for any suggestions/ideas you can toss my way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I read info from different worksheets into a summary sheet? | Excel Worksheet Functions | |||
reference to sheets without using sheet names | Excel Worksheet Functions | |||
Summarize multiple worksheet detail on summary sheet | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
Relative Reference | Excel Discussion (Misc queries) |