Forum Discussion
How to reference user selected value from a filter
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.
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)))
5 Replies
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?Using the same sample workbook:
=LET(items,SORT(UNIQUE(MAP(category,LAMBDA(e,IF(SUBTOTAL(3,e)=1,e,""))))),FILTER(items,items<>""))
