I have an excel file containing basically expenses and incomes and need this data to be consolidated (e.g. summed up; divided) in other tabs in particular ways.
a1) if the "Paid by" cell is equal to the "Expense of" cell, account the value in the right category (e.g. Supermarket) of the payer's consolidation tab.
a2) if the "Paid by" cell is different from the "Expense of" cell, consult tab "Sharing of expenses" to see how that particular expense is shared. For instance, the tab "Sharing of expenses" indicates that "Supermarket" expenses should be distributed at the 60-20-20 proportion. So if I register that I expend USD100 in this category, a formula should distribute this value among 3 people: 60 should go to a balance sheet of person A, 20 should go to a balance sheet of person B, and 20 should go to a balance sheet of person C.
b) if a payment is done with a particular credit card, a formula should check the date of the expense and "decide" whether to allocate the expense in the current month or in the next month.
There are other similar rules that must be implemented. Please, estimate your bid based based on the 3 items above only. Values for additional developments can be arranged afterwards.
One challenge is to keep the file not too heavy and slow.
The project will certainly need some "back and forth" between me and the selected provider until we reach the desired results.