Feb 17 2023 03:36 PM
I'm trying to create something for my place of work, but have hit a slight problem, and have been racking my brain for hours now, but im very out of practise with Excel and hoping someone can help.
I'm trying to retrieve a specific table of data, depending on what THREE separate dropdown boxes say.
For example, I have month, year and the type of activity, and then i have all the different stats for that activity, per month.
If i select e.g. 1st drop down says "May". Next drop down says "2022" and the last dropdown says "sports hall". this should then only show me the data table for that specific thing. If i change "May" to "February" then it should change to that specific months/years data table.
Anyone that can help me with this, id very much appreciate it.
Thank you in advance
Feb 17 2023 04:18 PM - edited Feb 17 2023 04:20 PM
The obvious way to do this might be to create a 4 column lookup table. First 3 columns are the attributes selected via the 3 dropdown inputs (data validation), the 4th contains the reference to the data table for every row (of permutations) as a string.
When you have your inputs, you can fetch the reference with eg
FILTER(Col4,(Col1=Input1)*(Col2=Inpout2)*(Col3=Input3))
Now that you have the reference, use INDIRECT to access.
Word of warning: This ain't great.
Feb 17 2023 04:30 PM
Feb 17 2023 04:46 PM
Feb 17 2023 11:24 PM
Feb 18 2023 02:41 AM
@ecovonrein For what it's worth, why not just =A1:B10 in E1. That also creates a spilled array. No need for VSTACK either.
Feb 18 2023 04:42 AM