I need to return a lookup on a table once a slicer has been selected.

[RockandGrohl]

Hi guys, two ways to look at a solution to this problem.

I have a scatter chart on a sheet called Curves, and the data for this chart is held on a sheet called Database. I have loaded up about 6 slicers for Database which are located on Curves. The user can select slicers in sequence to filter the data through different levels. Think of it like "Groceries > Fruit > Apple > Green > Granny Smith" or something like that

What I want to do is return a lookup once the last level is triggered. This slicer is called Asset as it pertains to the Asset column on the Database. I want a lookup to then use the Asset as the lookup value, against the Database table and return the result in the column called "Primary UoM". In the above example, once "Granny Smith" is selected it would return "Weight (g)" for instance.

I'm trying to think of a way to do it and have been spending all morning prompting ChatGPT but it's giving me fairly by-the-numbers solutions that involve basically checking the worksheet every time a change or calculation is made.

In my head, an ideal solution would be for a macro to detect column G (Asset) has been filtered in the Database tab, in which case the Primary UoM's will all be the same, and therefore just return the Primary UoM (column P) of the first visible row.

This doesn't seem to be possible, however, and therefore I'm left stuck. ChatGPT's solution could work, but looking at the code it looks incredibly fragile and I think functionality would stop as it requires EnableEvents etc, which I've found to be quite temperamental in the past.


Any ideas would be appreciated!

 

[JamesCanale]

Not sure about what you want, and I don't care for macros. I made up a table with a helper column and when there is only one item left after filtering, it shows the something. You can make it only show the first one using "TAKE" instead.

MrExcelPlayground23.xlsx
	N	O	P	Q	R
6	Item	Characteristic	Second	Weight (g)	Visible?
7	Food	Fruit	Apple	50	1
8	Food	Fruit	Pear	40	1
9	Food	Meat	Steak	250	1
10	Food	Meat	Chop Meat	250	1
11	Clothes	Shirt	Good Shirt		1
12	Clothes	Shirt	Bad Shirt		1
13	Clothes	Pants	Grey Pants		1
14	Games	Board	Monopoly		1
15	Games	Sport	Baseball Bat		1
16
17
18
Sheet5

Cell Formulas
Range	Formula
R7:R15	R7	=AGGREGATE(3,5,[@Item])
P18	P18	=LET(a,FILTER(Table6[Weight (g)],Table6[Visible?]),IF(ROWS(a)=1,a,""))

 

[RockandGrohl]

Not sure about what you want, and I don't care for macros. I made up a table with a helper column and when there is only one item left after filtering, it shows the something. You can make it only show the first one using "TAKE" instead.

MrExcelPlayground23.xlsx
	N	O	P	Q	R
6	Item	Characteristic	Second	Weight (g)	Visible?
7	Food	Fruit	Apple	50	1
8	Food	Fruit	Pear	40	1
9	Food	Meat	Steak	250	1
10	Food	Meat	Chop Meat	250	1
11	Clothes	Shirt	Good Shirt		1
12	Clothes	Shirt	Bad Shirt		1
13	Clothes	Pants	Grey Pants		1
14	Games	Board	Monopoly		1
15	Games	Sport	Baseball Bat		1
16
17
18
Sheet5

Cell Formulas
Range	Formula
R7:R15	R7	=AGGREGATE(3,5,[@Item])
P18	P18	=LET(a,FILTER(Table6[Weight (g)],Table6[Visible?]),IF(ROWS(a)=1,a,""))

View attachment 112080

I feel like this is the right track.

When the column with "Granny Smith" gets filtered, there will actually be numerous rows, but I'm only interested in getting the lookup working on the first visible row. Would your method be able to do that?

 

[JamesCanale]

In P18 of my example, if you put in:

=TAKE(FILTER(Table6[Weight (g)],Table6[Visible?]),1)

That will just take the top row of whatever the filtered (or unfiltered) table shows.