Forum Discussion
HELP ON REFERENCING ANOTHER WORKBOOK
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 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.
You wrote:
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.
Which basically supports my thesis: you're doing the heavy lifting by separating everything as you've done it, making it harder for Excel now to do this big picture analyses.
It would, as SergeiBaklan correctly points out, require a total redesign to make it into a single database--but doing that would provide maximum flexibility: you'd then be able to continue doing your daily and weekly summaries as well as the big picture stuff.
mathetes ok thankyou. Not the end to this story I was expecting but take your advise on board. Looks like I have a lot off work to re-do this. The reason I guess I have ended up in this position is it has evolved over time. To me wanting a quicker way to calculate what I earned in a day which is complicated in itself. To then keeping a weekly copy of it too check my payslip when I get it a fortnight later to now thinking it'll be good to see this information for the year in one place.
If I understand you correctly. I need to move to one sheet = 1 week. Therefore 52 sheets for the year with the summary on the 53rd sheet. which will require the wages calculation to appear 5 times on one sheet.
Another option could be Power Query, but that's rebuild the model from scratch.
