Forum Discussion
HELP ON REFERENCING ANOTHER WORKBOOK
You might use the INDIRECT function for this, but that only works if the workbook referred to is open in Excel, so you'd have to keep WK01.xlsx to WK10.xlsx open.
Let's say WK01 is in cell A3.
The formula in B3 could look like this:
=INDIRECT("'["&A3&".xlsx]Sheet1'!A1")
This can be filled down.
ok thankyou, is there a way to do something similar where the wookbooks didn't have to be open. As this would progress to 52 weeks over the course of the year and thats a lot off wookbooks to have open at any one time just to compare and add up over the course of the year?
You may check this Referencing value in a closed Excel workbook using INDIRECT? if something will help, I didn't test what is suggested in it.
You haven't said exactly how large those weekly workbooks are, how many distinct spreadsheets there are in each weekly workbook.
So let me ask a question of two:
- would it be possible to combine them as separate sheets all in the same workbook?
- might it even be possible to combine all of the data into a single data table (using column headings to differentiate days or weeks)?
It's been my observations that many people manually separate the raw data (the input end of things) into days or weeks for clarity at the front end. Excel is really good (it "excels") in taking a single database (a.k.a. Table) and summarizing it, breaking it apart as needed for detail output reports, tabulating things.
The Pivot Table is but one example of that....your summary presentation could easily be produced if all that data were in a single table to begin with, letting Excel do the heavy lifting of producing the summary numbers by day and week.
each workbook/week is 78kb each day has its own sheet that calculates what I have earned depending on multiple factors with a summary/total sheet at the end. There's then other sheets with references to pay rates drop down boxes and other sheets that determine variables on pay scales depending on certain circumstances.
This has all worked really well so far. I am now wanting another workbook where I can compare analyse these weeks in one place.
