Forum Discussion

LilYawney's avatar
LilYawney
Brass Contributor
Apr 06, 2023

Auto populating in table #SPILL! error

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?

 

    • LilYawney's avatar
      LilYawney
      Brass Contributor
      Those solutions won't fix the problem since the data needs to be in a table; I'm using slicers to filter out the large amounts of data that are going to be inputted.

Resources