I am developing a budget system that has group functionality for the categories - allowing the user to group categories.
I have a background sheet representing the year for the data that stores the data as laid out from the main budget sheet. I do not want to manipulate this raw data as I will have code to copy and paste (values) as is.
On the background sheet, I have the months laid out for the year and want "all" laid out as well. My plan is to have my code load the selected month from the background sheet to the budget sheet (not a problem) and load all months when "all" is selected. However, groups/categories can be added/removed/edited for specific month(s).
(In the picture, I separately selected the different amount/sized groups for JAN and FEB)
I want the groups/categories to be merged smartly when "all" is selected. I need a formula on my background sheet to 1), list all unique categories for the year, 2) list each category under the group it used to be/is in, and 3) match the most recent values/remarks to each category.
• I have 4 types (Income, Expenses, Debt, and Savings) laid out vertically, I'm only worried about Income and/or all for now until I figure out this merging problem.
• When "all" is selected on my budget sheet, even if a group/category is not on the most current month, it will still be displayed.
• If a category was in existed in 2 months but in different groups, display as listed under the most recent month.
• Attached is the background sheet file with the relevant data.
I would be very grateful for any solution to make this spill formula happen. Thank you!