I am using a Table with slicers. The user selects the info from slicer #1 to see the worksheet filtered according to selection. I want to have a field display data from a reference table of budgets based on what was selected in the slicer. Is this even possible?
Hi Carolyn,
You can use this formula to return the value filtered by the Slicer (it will only return one value i.e. it cannot handle multiple values selected in the Slicer):
=INDEX(Filtered Column,MATCH(1,(SUBTOTAL(3,OFFSET(A14:A242,ROW(A14:A242)-MIN(ROW(A14:A242)),0,1)))*(A14:A242<""),0))
Enter with CTRL+SHIFT+ENTER if you have Excel 2019 or earlier.
Replace 'filtered column' with a reference to the column you want the value returned from. And replace A14:A242 with the reference to the rows for the first column of your table.
If you expect multiple values to be selected in the Slicer, then you should use a PivotTable instead. You can put every field in the table into the PivotTable and set the layout to tabular so it looks very similar to a table. This way you can build another PivotTable that lists the items selected in the Slicer.
Mynda
