Need some syntax help:
I have a stand alone worksheet / file where I am summarizing information from an external workbook. The external workbook has individual tabs, all with the same information and format, but different values. For example, the external workbook has tabs for Monday, Tuesday, Wednesday, etc. The information documents time spent working, driving, sleeping, etc adding to 24 hrs.
The table in the separate summary file is on a single tab labeled 'summary,' and creates a single table with all of that information. In the summary table, the reference has the form:
='[Reference file example.xlsx]Monday'!$B2
To save time, I want to replace the tab name (Monday above) with my own text, preferably from a table within the same spreadsheet. This way, I can copy the link, and reference the local table that contains the proper tab name.
Thus, within my summary sheet, I'd have a vertical table of cells that contain text like Monday, Tuesday, Wednesday. If I created a new tab in the external file called Saturday, I would drag the formula to the right, and it would create a new cell with the name Saturday. I could then drag down the columns to fill in the information (thus the absence of a $ in front of the 2 in the formula above.
The external reference workbook would look like this. There would be individual tabs, each for a day of the week:
| Time Card | |
| | Monday |
| Working | 8 |
| Driving | 2 |
| Sleeping | 8 |
| Loafing | 6 |
| Total | 24 |
The summary worksheet, in a stand alone file, would look like this:
| | Monday | Tuesday | Wednesday |
| Working | 8 | 6 | 12 |
| Driving | 2 | 1 | 2 |
| Sleeping | 8 | 8 | 8 |
| Loafing | 6 | 9 | 2 |
| Total | 24 | 24 | 24 |
| | | | |
| Monday | | | |
| Tuesday | | | |
| Wednesday | | |
| Saturday | | | |
Dragging the cell with the heading Wednesday would do 2 things:
- Continue the reference to the external workbook.
- Contain the reference to the new tab in that workbook with the name Saturday from the table below it.
So, each time someone updates the values on the tabs in the external workbook, the summary values would also change.
This is an example of what I'm doing, not the real thing. I'd be using the same type of cell references in the formulas, though. I want to do this without using macros...
Edit - The table with the days of the week can be in a row instead of a column, so I can drag the formula to the right and retain the cell references.
Any ideas? Thank you!
