Oct 03 2022 12:48 PM
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.
Oct 04 2022 08:17 AM
SolutionThis 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)))
Oct 04 2022 09:07 AM
Oct 04 2022 09:14 AM
Using the same sample workbook:
=LET(items,SORT(UNIQUE(MAP(category,LAMBDA(e,IF(SUBTOTAL(3,e)=1,e,""))))),FILTER(items,items<>""))
Oct 05 2022 10:10 AM
Oct 05 2022 10:40 AM
Oct 04 2022 08:17 AM
SolutionThis 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)))