SOLVED

How to reference user selected value from a filter

Copper Contributor

I am trying to reference the item a user picks from a filter. I cannot use a drop down list. 

 

For example, there is a column where a filter is applied. User selects 1 item in the list. How can I reference what the user picked in a formula?

 

I am trying to subtotal hotel data based on the selected property. The data will include multiple properties, user selects one, data displays for just that property. I cannot use subtotal as this data also needs to be separated by month and there is no Subtotalif function. 

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Crawforc 

This might work if you only need to reference 1 selected item in the filter (If you need multiple, another formula can be drawn up).

 

=REDUCE("",category,LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,v)))
Yes, this works!

If you don't mind, what would it be if multiple items were selected?

@Crawforc 

Using the same sample workbook:

=LET(items,SORT(UNIQUE(MAP(category,LAMBDA(e,IF(SUBTOTAL(3,e)=1,e,""))))),FILTER(items,items<>""))
Thank you so much, these both worked beautifully.
You're welcome. Glad it worked!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Crawforc 

This might work if you only need to reference 1 selected item in the filter (If you need multiple, another formula can be drawn up).

 

=REDUCE("",category,LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,v)))

View solution in original post