Forum Discussion
calling an entire table, depending on the dropdown lists selected.
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
- The way Excel might like better is much more difficult to do. Suppose you have a data table of 10 rows by 2 columns in A1:B10. You can duplicate this via MAKEARRAY, making a perfect copy - say out of E1 (spilling to F10). This will seem like a complete waste of time. But instead of having to access this table as A1:B10, you now have the luxury of referencing it as E1#. Now put a label next to the top of the table in D1. Continue down column E installing further tables from MAKEARRAY (or similar SPILLs). Name them all in D. Their dimensions don't matter - they can all be different widths, different lengths.
Create the same lookup table as before but instead of associating Excel range references, now associate the names in D. One you have FILTERed to the desired table handle (as before), do
=INDEX(E:E,MATCH(name,D:D,0),1)#
and you have access to the complete table "name".
This is messed up but it avoids INDIRECT. It is also less tragic if the data tables are perhaps imports from some data base and might arrive as SPILLs in the first place. (In that case, you obviously do not need to copy them via MAKEARRAY.)- Forget MAKEARRAY. The easier way to convert a range into a SPILL is like =VSTACK(A1:B10). Done.
ecovonrein For what it's worth, why not just =A1:B10 in E1. That also creates a spilled array. No need for VSTACK either.
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.- Thank you for this. I will try this out in the morning and send another reply once I've tested it
