Creating an automatically changing spreadsheet copy

[Kondho]

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

[#2]

@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.

[#3]

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

[#4]

As you see in the screenshot, one table wouldn't make it

[#5]

@Kondho Yeah. Have looked at the screenshot. not very useful, though and it confirms my suspicion. The way you have set up file is just not optimal and makes analysis difficult.

[#6]

Well, to me this looks good and quite easy to understand. There's a lot of information which is the reason I need many spreadsheets. Just can't share everything here since it would require too many screenshots and wouldn't actually provide much valuable information here.

I could just make two different but identical spreadsheets but changing both each time would make it a bit annoying. And there would be an increased risk for making a mistake when trying to modify both the exact same way.

[#7]

@Kondho Your choice. Good luck!

[#8]

@Kondho 

I looked at your screenshot, and Mr Van Ekelen is correct, you should maintain only one transaction table, and maybe several dim tables.

then you will create a data model so everything is dynamic (automatic) and easy to maintain.

The tables in the screenshot you provided would represent your future analysis pivot tables, you can create an income pivot table, an expense pivot table, a pivot table for taxes, a pivot table for your actuals budgeting, and a pivot table for your forecast budgeting..  etc.

When you update items or add items in your category dim tables for example, you only have to add, or update in one place and it will propagate in your transaction table as well as all your analysis pivot tables.

cheers