I am working on an expense spreadsheet. All expenses and their info are dumped into a sheet called "Itemized Expenses"; on another sheet called "Monthly Expenses", I want it to auto populate the G/L Code, SBU, and Category into the table. The way I've been doing that is by having the following codes in the G/L Code column, SBU, and Category, respectively:
=IF([@SBU]="","",IFS([@SBU]=Sheet1!$U$2,Sheet1!$T$2,[@SBU]=Sheet1!$U$3,Sheet1!$T$3,[@SBU]=Sheet1!$U$4,Sheet1!$T$4,[@SBU]=Sheet1!$U$5,Sheet1!$T$5,[@SBU]=Sheet1!$U$6,Sheet1!$T$6,[@SBU]=Sheet1!$U$7,Sheet1!$T$7,[@SBU]=Sheet1!$U$8,Sheet1!$T$8,[@SBU]=Sheet1!$U$9,Sheet1!$T$9,[@SBU]=Sheet1!$U$10,Sheet1!$T$10,[@SBU]=Sheet1!$U$11,Sheet1!$T$11,[@SBU]=Sheet1!$U$12,Sheet1!$T$12))=IF(D6<>"", UNIQUE(FILTER('ITEMIZED EXPENSES'!$G:$G, 'ITEMIZED EXPENSES'!$J:$J=D6, 'ITEMIZED EXPENSES'!$G:$G<>"")), "")=IFERROR(INDEX(UNIQUE(FILTER('ITEMIZED EXPENSES'!J:J, 'ITEMIZED EXPENSES'!J:J<>"")), MATCH(0, INDEX(COUNTIF($D$5:D5, UNIQUE(FILTER('ITEMIZED EXPENSES'!J:J, 'ITEMIZED EXPENSES'!J:J<>""))),,), 0)), "")
Explanation of formulas' desired functionality:
G/L Code - return the G/L Code of the matching category from "Itemized Expenses" (should return values on the same row)
SBU - return a unique list of SBU's based on the category within each SBU from the "Itemized Expenses" sheet
Category - return a unique list of Categories from the "Itemized Expenses" sheet
The problem I'm running into is that each SBU has their own category; but some SBU's share similar categories. (i.e., Corporate Services and General and Admin SBU's both have their own "Office Expenses" category).
(G/L Codes and "Requested By" have been hidden due to sensitive information; "Check Amount" isn't hidden since true numbers were changed for this image)
Because of this, I get some '#SPILL!' errors since the formula is trying to return a list of all SBU's that match that category.
The whole reason why the expenses need to be organized this way to calculate the monthly expenses; there will be multiple expenses for each SBU Category so we want them to be divided out (hopefully that makes sense)
Is there a way to fix this '#SPILL!' error by fixing my formulas? Or should I populate the information differently?
