Unique Formula for visible cells only

[Chewyhairball]

Hi

I am using the unique formula to extract a list of unique values from a table and I have to say I am a big fan of this formula having just discovered it.

=UNIQUE(Table5[[#Data],[Building]])

Is there a way to use this formula ( or similar) to extract a unique list from visible rows only?

When I have other filters applied it would be good to be able to extract only the visible rows to create the unique list. I use this column to create a drop down list so I guess what my goal is is to
create a dynamic dropdown list.

I am not looking for VBA for this rather trying to find out if there is a way to do it with a formula.

thanks

Rory

Rory

 

[jasonb75]

I would say use a helper column with a subtotal formula for the row, for example

=SUBTOTAL(2,[@Data])

Then change the unique formula to

=UNIQUE(FILTER(Table5[[#Data],[Building]],Table5[Helper]=1,""))

Think that is right, but without setting up a table to test the syntax, there could be any number of errors

 

[Chewyhairball]

Thanks jason. Ill give it a bash and let you know how i get on

 

[Fluff]

Whilst I'd go with the helper column that Jason has suggested, another option would be

=UNIQUE(FILTER(Table5[Building],SUBTOTAL(3,OFFSET(Table5[[#Headers],[Building]],ROW(Table5[Building])-ROW(Table5[[#Headers],[Building]]),0))))

although this is volatile, which could have an adverse affect on the performance of your workbook.

 

[Chewyhairball]

Hi, I went with the helper table and it is perfect for what i need. Thank you both for the replies

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[duncanwil]

Whilst I'd go with the helper column that Jason has suggested, another option would be

=UNIQUE(FILTER(Table5[Building],SUBTOTAL(3,OFFSET(Table5[[#Headers],[Building]],ROW(Table5[Building])-ROW(Table5[[#Headers],[Building]]),0))))

although this is volatile, which could have an adverse affect on the performance of your workbook.

I tried this version on my Power Query output table that I had linked to a histogram and a slicer and it worked perfectly. Thank you