SLICER Selection into a cell

p45cal · #8 04-24-2024, 03:09 AM

Quote:

Originally Posted by Michele_1979

I would need to view the slicer selection into a cell. I

Not straightforward. I'm sure there's a convoluted solution using cube functions and the data model, but before I explore that (I've never done it before) there may be a simpler solution that might do for you.
Its fault is that it shows not just the slicer selection of just one column of your table but the resulting unique values in one column of all the filters that may have been applied to any columns of that table.
Spilling list:

Code:

=UNIQUE(FILTER(Table1[Hdr1],SUBTOTAL(103,OFFSET(Table1[Hdr1],ROW(Table1[Hdr1])-MIN(ROW(Table1[Hdr1])),0,1))))

One cell list, same but:

Code:

=TEXTJOIN(", ",FALSE,UNIQUE(FILTER(Table1[Hdr1],SUBTOTAL(103,OFFSET(Table1[Hdr1],ROW(Table1[Hdr1])-MIN(ROW(Table1[Hdr1])),0,1)))))

See attached.

ps. shorter versions of those formulae:

Code:

=LET(a,Table1[Hdr1],UNIQUE(FILTER(a,SUBTOTAL(103,OFFSET(a,ROW(a)-MIN(ROW(a)),0,1)))))
=LET(a,Table1[Hdr1],TEXTJOIN(", ",FALSE,UNIQUE(FILTER(a,SUBTOTAL(103,OFFSET(a,ROW(a)-MIN(ROW(a)),0,1))))))

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to move a selection one cell up using VBA?	Bumba	Excel Programming	6	11-19-2019 08:50 AM
How to trigger a macro on cell selection	Larry_1	Excel Programming	6	12-24-2017 11:09 AM
Limit min and max number to a cell based on country selection	hussainshaikh1	Excel	1	02-02-2017 07:06 AM
Selection from cell with list contents autopopulates contents of another cell	markharper80	Excel	3	02-16-2015 04:18 PM
ms office excel 2007 cell selection issues	pavamaklava	Excel	1	05-12-2010 10:47 PM