Log In
Register
Home
January 17, 2024
Offline
Hi everyone,
I would like kindly to ask for some help.
Problem: I would like when I click on the Slicer buttons to condition my dropdown list, which is returning a picture. / For this purpose I have used the XLookUp formula/. If the first item from the Slicer is selected, the product from the dropdown list, which is also in first position shows up, therefore I can see the picture. However, not all Categories ( defined by the Slicer) contain the same amount of products.
Question: Is there a possibility to setup the dropdown list by default to show the very first Item, when the Slicer tab is selected? I have tried - Vlookup, Filter, and Switch functions, but so far without a result.
Could it be that my approach is just wrong?
Note: I am using Office 365
Link: Test_Example.xlsx
Thanks in advance,
Greetings
Tanique
See Answer
Trusted Members
October 17, 2018
Offline
I'm not sure if it works but you could generat a list in with the resulsts and link the Dropdown to this range.
You have not mentioned which version you are using but I cannot work with your file because all the pictures are UNKNOWN for my system.
January 17, 2024
Offline
"Could it be that my approach is just wrong?
Note: I am using Office 365"
That is the version I am using. Office 365., please do let me know If I have to add some more information.
The pictures can be found here:
https://1drv.ms/f/s!AuUWSgf3FX.....p?e=arJA3B
I apologise if too much trouble 🙁
Trusted Members
October 17, 2018
Offline
I'll try and see if I can help you
Trusted Members
October 17, 2018
Offline
I've got the pictures but the file refers to a pivot table whihc is not available.
Sorry for that. No help from this side
January 17, 2024
Offline
Hello,
Thank you for the effort.
I am not sure what you mean.
Please could you refer to which pivot table there isn't a connection? So far it has all been placed on one sheet. And to me even if I work only through one drive, not as a downloaded file on a desktop, the dashboard is still working.
I would like really to solve this issue and perhaps I will try to recreate it again with a few pictures only.
Tanique
January 17, 2024
Offline
Hello Hans,
I looked at the .excel file carefully and uploaded it again. Below I posted some pictures for clarification.
Greetings,
Tanique
Picture showing XlookUp formula referring to Table named "Source".
Picture showing "Slicers" referring to the table named "Products".
Picture showing Data validation / Dropdown list / referring to the table Products, first column.
Link here: Test_Example.xlsxhttps://1drv.ms/x/s!AuUWSgf3FX.....9?e=ee6jeC
Moderators
January 31, 2022
Online
I took the liberty to change your set-up slightly. See attached and let me know if this works for you.
Had to remove the pictures as the file is too big otherwise. But it works as you intended if you put them back in.
Answers Post
January 17, 2024
Offline
Hello Riny,
Thank you so much! I just tested it with the pictures and it works, exactly as I wished it to.
I have noticed that one of the pivot tables "Products" is removed, and therefore the Slicer is made from the Insert menu. But perhaps that should not matter for my original file.
Additionally, one more collum has been added to the table "Source" and then =Subtotal(3,[@Category]), which gives a result of 1. I have to study this because I am not so advanced.
Thanks once again
I wish you a lovely day.
Greetings,
Tanique
Moderators
January 31, 2022
Online
Ah. forgot to mention that extra column. It's to enable the filter function to pick-up only visible rows fro the data table when you have made a selection with the slicer.
January 17, 2024
Offline
Hello,
it's alright. Actually, the approach is so much simplified than what I was trying to do over a week. Sometimes simple things work the best. 🙂
Greetings,
Tanique
Trusted Members
Moderators
November 1, 2018
Offline
FWIW, if you don't want the additional column, you can amend the FILTER formula to do the same work:
=FILTER(Source[Name Product],BYROW(Source[Category],LAMBDA(r,SUBTOTAL(3,r)=1)))
January 17, 2024
Offline
Hello Velouriaa,
thanks for coming to the topic with another suggestion. I will try to test also your example in more detail.
Because I am using Office 365 Beta, today both functions are not accepted and it appears an error... ;(
January 17, 2024
Offline
Hello,
I have done many tests and tried to educate myself as much as possible. The problem is half solved.
If I don't select another item from the dropdown list ( by means not the first one ), I come to the same problem again. When choosing the Slicer, the dropdown list does change with ranged items, but the first item for a preview as a default is already not working.
Here is the picture. If the previous Slicer Radios has been selected, then moving to the next Slicer Gadgets, I have to click on the dropdown list to see the item. My whole idea was the possibility of automating exactly this very transition as a default to be the first item.
1 Guest(s)
