Boy, this is a hard one for me! FORMULA SOS

[britt635]

Hello! I am trying to create an updated expense tracking spreadsheet for work that will help our department save TONS of time in the long run, but I am struggling. I am by no means an expert, but usually I can research how to videos and make it happen. After HOURS of videos and trial and error, I landed here. This is just so confusing and I am hoping someone can help tell me how to do it, or, edit the sheet with the basic formulas to get me started so I can figure it out from there. Anything is helpful!

The problem I am trying to solve: Right now we have a very similar sheet as the one attached (minus the tabs on the bottom) and every time we put an expense in, we have to find and open another document matching the correct budget and basically copy all of the same information over to track it. We have 20 people, multiple budget categories, and thousands of expenses each year so this just becomes an absolute mess and waste of time to do. 

The goal: When we add our expense to the main "2023 Budget" sheet, depending on the drop-downs we choose for each column, it will automatically add the total in the correct sheet (which have now been added as tabs at the bottom vs separate documents), for the corresponding line item, in the budget actuals for that month. 

Note: I have completely simplified the attached version to limit confusion. 

Specifics: Each tab after '2023 Budget' represents the name of a different budget. 

• On the 2023 Budget Tab > Under the budget column, only the budget names (aka names of tabs) should appear as a dropdown. 
• In the Sub Budget column, only the headings for the selected budget sheet should appear as a drop down list option. (This seems silly but we have up to 20 sub categories in some budgets)
• In the Line Items column, the only items that should show are those under that previously selected Sub Budget column. 
• When typing in the amount, this should automatically be put in the correct tab/budget>sub category > line item > under 'actuals' for the correct month, dependent on the date in the first column. 

For example: On the 2023 Budget tab, If I put Date = 1/1/2023, Budget = FinEd, Sub Budget = Schools, Line Item = Curriculum, Amount = $50, it will automatically put $50 into the budget actual column in that sheet under January. If subsequent items are added for the same month and line, that total will just get added within that tab. 

I know this is A LOT to ask for but if anyone can help me out, I promise you I will pay it forward however I can. Thank you so much!

[#2]

@britt635 I would strongly recommend you to re-think the whole process. You are trying to capture transactional information is a reporting style. That's not going to be easy to maintain.

In the attached file I added Sheet1 where I added a small part of the FinEd budget and some Actual expense rows in a structured Excel table. Everything in one table!!

Then, I built a pivot table on that table to produce a very basic Actual vs. Budget report. Keep adding rows to the table and refresh the pivot table and all will be updated almost instantly.

I didn't go as far as to putting in all data validation options, but you can do that yourself. Bear in mind, though, that it's not wise to hard code all the options into the DV list. Better to create, for instance, a list of employees somewhere and point the DV list to that range of cells. Much easier to maintain if people need to be added.

Now, this is a very simple example. You mentioned that you also simplified your data example. So, it's probably not going to work for you without adjustments, but I hope you get an idea of what's possible, provided you capture data (budget and actual) in the correct way from the start.

If you really want to go a bit more sophisticated, you could have one table for the budget and one for the actual and "connect" them via relationships in a Data Model.