Slicer issue in combination with Dynamic array

Question

Hi

Why does the slicer filter "Brian" return "Lone" in the dynamic filter array created with UNIQUE/FILTER?

Regards, Henrik

Answer 1

Hi,

I created a sample here, I cannot reproduce this problem.

I suggest you clear all formulas in E column and F column, then re-enter the formula:

=UNIQUE(FILTER(tTable[name];RIGHT(tTable[name];1)<>"b"))

=COUNTIF(tTable[name];E2)

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi. You must use '#', that is the symbol for referencing a dynamic filter array, like E2#. '#' secures, that the referenced area expands, when the source data expands, and thats a wonderfull new thing in Excel, and important in my real life scenario.

Answer 3

FYI, I found this to make slicers filter array formulas, very usefull! https://exceloffthegrid.com/using-slicers-with-formulas/