Forum Discussion
Creating an automatically changing spreadsheet copy
Hey!
I tried to find a solution for this problem in the Internet but I couldn't find any or maybe I just can't describe my problem
So I am creating a budgeting tool with Excel. I have a spreadsheet for the budget which I will fill in in advance and then I have another spreadsheet for the actual expenses which I will fill in afterwards.
These two different spreadsheets will then be compared in a third spreadsheet which will show the difference and the results of how well I managed to budget. Like budget vs. actually happened.
These two spreadsheets that I am creating have to look exactly the same with the same formulas, the same layout and same formatting. The only difference will be the the actual numbers I am going to fill in.
The problem here is that I need to make a copy of a spreadsheet. But the copy has to change all the formatting and formulas according to the original one. (For example if I am making a new expense category in the budget spreadsheet, the same category has to appear in the actual spreadsheet.) This is because I will make some changes to the original (budget) spreadsheet along the way as I am filling it in. These changes has to happen in the copy as well or otherwise it will make a mess and I don't want to make all the changes twice.
If I simply copy the spreadsheet, only the current content is copied but it will not change when the original spreadsheet is modified. So I guess this method doesn't work here.
I hope you understood my issue here.
Thanks in advance
7 Replies
Kondho As you have already experienced, it is difficult to keep three separate spreadsheets in sync, when you make changes to one of them. Personally, I would maintain two tables in the same workbook. One with budgeted amounts (probably per reporting period) and one with all actual transaction amounts by date or perhaps already summarized per reporting period. Both tables have only a limited amount of columns. One of these would be the income/expense category.
Then, I would make one sheet with the report lay-out (your third one, I believe) with the budget to actual comparison that is linked with e.g. SUMIFS to the budget and actual tables.
If you want to add an expense category to the budget, make the change to the budget table, add a line-item in the report-layout and adjust formulae as required, once. When actual expenses get reported in the new category, they will be picked-up in the report automatically.
Easier said than done, but not too difficult either. It all depends on the structure of your data. How do you get the actual expenses into Excel, for instance? Set it up correctly once and you should not have to spend too much time on it when changes are needed.
A perhaps overly simplified example in the attached workbook.
Thanks for a reply.
Only the two spreadsheets have to be identical and not the third one as it will have different structure.
I really need to have the data in different spreadsheets (both still in the same workbook) as the there will be lots of categories (as rows) and months (as columns). If I make everything in one spreadsheet it would be a mess. Actually there will be around 6 different spreadsheets in total for different purposes (monthly budget, monthly actual, savings, monthly summary, yearly summary etc) So the need for different spreadsheets is real.
The actual expenses I will just fill in afterwards by going through the receipts and bank statements.
Perhaps I have to make it really well once so there's no need to modify it later. But I know I will have to make changes as my life situation changes for instance.
Kondho- As you see in the screenshot, one table wouldn't make it
