I have a sheet using a spill range of product groups. However the groups are relatively detailed. IE: Pencils Black, Pencils Green, Pens Black, Pens Blue. There are 57 different detailed groups. I have a table that lists these items and concise groups. IE: Pencils Black = Pencils, Pens Black = Pens, Erasers = Misc. There are only 4 final groups.
I am trying to filter the Item Group table using the spill range (it's one column of data) as criteria and I get a #N/A error.
Here is the formula: =FILTER(tblItemGroup[Item Group],tblItemGroup[Item Kind & Level 1]=D8#) If I change the formula to the cell reference (D8) instead of the array, it works fine but only for that row. I can copy that formula but then it is not dynamic and that will affect other calculations.
But I need these results to be dynamic as the amount of rows will vary based on another selection on the sheet (Salesman).
