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. ...
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.)
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.
- Spot on. Because I am dumb. Mind you, the whole operation (of duplicating a table) is kind-of dumb. But it redeems itself at the point of lookup, I guess.
On that note SGTerry: The MATCH(D:D... might be slow. Excel will prefer when you do D1:Dn, ie limit the span to something meaningful.
