Forum Discussion
Creating an automatically changing spreadsheet copy
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
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.
