How to fix the #Spill! error when using the Filter function

[Gpaul]

I am trying to use the Filter function to pull a subset of just the ID's and Names from the first table if the are a producer (in column D) and populate the subset into the first 2 columns of the second table. I am getting the #Spill error and I think it is because I am using 2 tables. If I use the below formula and use it on another tab thats not a table it works fine.

Any ideas??? I can't figure out a solution.

The formula that I am using = =FILTER($A$4:$B$35,$D$4:$D$35 = "Producer")

 

[Gpaul]

Hitting F9 shows the formula is calculating correctly....however it is erroring out when it try's to display the results

={"10000496","Test 1";"10001948","Test 2";"10001949","Test 3";"10001950","Test 4";"10000514","Test 9";"10000517","Test 10";"10001875","Test 11";"10000502","Test 12";"10000504","Test 17";"10000527","Test 18";"10000975","Test 19";"10000531","Test 20"}

 

[jaeiow]

Do you want to keep it in a table? Then you have to pick a single item for a single table row. Otherwise, remove the table (and also any cell interfering with the dotted spill range).

 

[Gpaul]

Yes it is a budget workbook and each tab is a table. Not sure what you mean by pick a single item for a single table row. I did find on a google search that arrays don't work in a table??

 

[jaeiow]

Yes, they do not work on tables, so remove the table. Or, "pick a single item" with the Index(array,row,column) format.

 

[Fluff]

Why not just convert Table2 into a range?

 

[Gpaul]

@Fluff
I have to have it as a table as it is part of an import into a SQL data base. Not optimal but I did just do the calculation to the side of the table and referenced the results.

Thanks for everyone's responses!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[jaeiow]

To keep it in one table

G4 :

=INDEX(FILTER($A$4:$B$35,$D$4:$D$35 = "Producer"),ROW()-3,1)

G5 :

=INDEX(FILTER($A$4:$B$35,$D$4:$D$35 = "Producer"),ROW()-3,2)

 

[Gpaul]

Thanks jaeiow that solution worked for me.