The attached file has some further approaches.
View attachment Return a range based on two dropdowns 20130628.xlsx.
Assuming you have Excel 2007 or greater, if you put your lookup lists in an excel table called Table1 you can array enter this into columns C to K and copy down:
=IF(AND(A1<>"",B1<>""),TRANSPOSE(INDEX(Table1,,MATCH("Blades "&$A1&B1,Table1[#Headers],0))),"")
Note that 'Array Enter' means you need to select the range C1:K1, then click in the formula bar, paste the above formula, and push Cntr + Shift + Enter.
That first bit in bold stops any errors showing in the event that users haven't yet made a selection.
If you have Excel 2003 or earlier, you can array enter this VOLATILE function into columns C to K and copy down:
=IF(AND(A1<>"",B1<>""),TRANSPOSE(INDIRECT("Blades_"&A1&B1)),"")
More on Volatile functions (and the problem I have with them) at the end of this post. But meanwhile, in this particular case, there are several other good alternatives to using volatile functions.
This one simply requires your lists to all be in a named range called ‘data’. You
don’t need individual named ranges such as Blades 27m42, Blades 34m42 or Blades 06m42.
=IF(AND(A1<>"",B1<>""),TRANSPOSE(INDEX(data,2,MATCH("Blades "&$A1&B1,INDEX(data,1,),0)):INDEX(data,10,MATCH("Blades "&$A1&B1,INDEX(data,1,),0))),"")
This one doesn’t care
where your named ranges are. They can even be scattered across different sheets:
=IF(AND(A1<>"",B1<>""),TRANSPOSE(CHOOSE(MATCH("Blades "&A40&B40,Categories,0),Blades_27m42,Blades_34m42,Blades_6m42)),"")
So what’s the problem with volatile functions? Basically volatile functions such as INDIRECT, OFFSET, TODAY, NOW, RAND, RANDBETWEEN and a others can slow down calculation on large spreadsheets.
What that means is that if you have volatile functions in your workbook, any time you make a change anywhere at all on the spreadsheet, Excel recalculates the value of any the volatile functions too. Excel then recalculates every applicable formula downstream of these functions too – regardless of whether anything upstream actually changed or not.
That last part – “
regardless of whether anything upstream actually changed or not” – is worth a demonstration.
• Say you have the function =TODAY() in cell $A$1. Obviously that value is only ever going to change once per day, at midnight.
• Say you have ten thousand formulas
downstream of $A$1 – that is, they either refer directly to $A$1 or to one of $A$1’s dependants. Those ten thousand formulas will get recalculated
each and every time any new data gets entered anywhere on the spreadsheet, even though the value of $A$1 itself only changes one per day!
For this reason, too much reliance on volatile functions can make recalculation times very slow. As Charles Williams (Excel MVP who is an expert on worksheet bottlenecks) "
Use them sparingly. Try to get out of the habit of using them at all". In fact, there are usually alternatives to every volatile function (sometimes requiring VBA code), including INDIRECT.
For that reason, I've got into the habit of NEVER using Volatile functions wherever possible –
even if performance
isn't going to be an issue – just so I don’t fall back on using them when performance
is going to be an issue.
But that’s just me. There’s nothing intrinsically ‘wrong’ with Volatile functions in many (most) circumstances. Now I better get down off my soap box ;-)