How can you display a list of data if two conditions are met in a cell but not as a drop down List with a varable length of data: ie
if a1 =27 and b1 =M42 then list " Blades27m42"...... "Blades 27m42" is a named range that has 9 items in.
if a1 =34 and b1 =M42 then list " Blades34m42"...... "Blades 34m42" is a named range that has 5 items in.
ii a1 =06 and b1 =M42 then list " Blades06m42"...... "Blades 06m42" is a named range that has 7 items in.
thank you in advance
You would need a formula in first cell dragged down 9 rows (max number of items in any list)....
e.g.
=IFERROR(IF($B$1=$M$42,INDEX(INDIRECT(LOOKUP($A$1,{"27","34","06"},{"Blades 27m42","Blades 34m42","Blades 06m42"})),ROWS($A$1:$A1)),""),"")
copied down 9 rows.
The attached file has some further approaches. 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 ;-)
Last edited by JeffreyWeir; 2013-06-28 at 06:02 AM.
Thanks, but could not get to work, sorry but "M42" is text not a cell ref , I changed to text but still could not get it to work. Can you explain the "ROWS($A$1:$A1) part pleaseOriginally Posted by NBVC
Sorry new to this !!
How can you display a list of data if two conditions are met in a cell but not as a drop down List with a varable length of data: ie
if e2 =27 and f2 ="M42" then list " Blades27m42"...... "Blades 27m42" is a named range that has 13 items in. ie c30:c42
if e2 =20 and f2 ="M42" then list " Blades34m42"...... "Blades 20m42" is a named range that has 11 items in. ie c17:c27
ii e2 =06 and f2 ="M42" then list " Blades06m42"...... "Blades 06m42" is a named range that has 2 items in. ie c2:c3
etc the use will type the width and type in cell e2 and f2, depending on this the list eill change in col I 2 down for the length of the list
How about?
=IFERROR(IF($B$1="M42",INDEX(INDIRECT(LOOKUP($A$1+0,{6,27,34},{"Blades 06m42","Blades 27m42","Blades 34m42"})),ROWS($A$1:$A1)),""),"")
the ROWS($A$1:$A1) is used to return a ROW number for the ROW parameter of the INDEX function, so first it will grab the item in the first row of your named range, then as you copy down it grabs, second, then third, etc....
The LOOKUP table range should have also been in ascending order... I mistakenly didn't have it that way in the first suggestion...
THANK YOU so MUCH, APPRECIATED, WORKS GREAT
THANK YOU so MUCH, APPRECIATED, WORKS GREATHow about?
=IFERROR(IF($B$1="M42",INDEX(INDIRECT(LOOKUP($A$1+0,{6,27,34},{"Blades 06m42","Blades 27m42","Blades 34m42"})),ROWS($A$1:$A1)),""),"")
the ROWS($A$1:$A1) is used to return a ROW number for the ROW parameter of the INDEX function, so first it will grab the item in the first row of your named range, then as you copy down it grabs, second, then third, etc....
The LOOKUP table range should have also been in ascending order... I mistakenly didn't have it that way in the first suggestion...
Can this formula be nested, to include :
if(f2 ="G42", or f2 = "S42" with the same lookop for e2 (as below) ie {6,10,13,20,27,34,41} but with different range names (as below) ie {"B6m42","B10m42","B13m42","B20m42","B27m42","B34m42","B41m42"} but with "B6g42","B10g42"..... etc and "B6s42",B10s42"...etc with the same ROW refs ie as below ROWS($C$2:$C2)
Im using this below for the spreadsheet and works great, however i need to extend it as above
=IFERROR(IF($F$2="M42",INDEX(INDIRECT(LOOKUP($E$2+0,{6,10,13,20,27,34,41},{"B6m42","B10m42","B13m42","B20m42","B27m42","B34m42","B41m42"})),ROWS($C$2:$C2)),""),"")
Many Thanks
Try this one for size:
=IFERROR(INDEX(INDIRECT("B"&$E$2+0&LEFT($F$2)&42),ROWS($C$2:$C2)),"")
copied down
That works a treat, beats the 3 rows of formula i had !!! Thank you for your help much appreciated, How long did it take you to be so proficient in excel, Brilliant
Can I format the list that displays with a colour background for the length of the list displayed, can i add to this with a border ?
Ps ....I can change font size... lol
Posting Permissions
Reply With Quote
Bookmarks