Excel 2007 : Drop down menu Help

[Hlowmaster]

I have attached an example.


Under the Proposal tab in E20. Whatever "number" they select in the dropdown ( will end up being 1-90) needs to make B20 's dropdown only certain numbers. The numbers are on the "entry" sheet. in example. If E20 is one. then B20 can only be 300,500,1000. I cannot have off numbers in B20 such as 305 or 299. Then if they select "2" under E20 then it would only show the quantity available for "2"

Thanks,

[NBVC]

You would need to name each subrange and then indirectly reference them

For example, you would name D5:D7 on the Entry sheet as "Choice1" (without quotes)... then D8:D10 would be "Choice2", etc...


Then in Proposal sheet, B20, you would do Data|Validation|List with formula:

=INDIRECT("Choice"&E20)


See here for more:

http://www.contextures.com/xldataval02.html

[JBeaucaire]

I added two DYNAMIC NAME RANGEs to your workbook. The first is PhotoNums and it is all the numbers in column A on sheet Entry. The second is QTY which is simply an offset of the PhotoNums, all the values over 3 columns.

Then I put this Data Validation formula in Proposal cell B20:

=IF($E20>0, INDEX(QTY, MATCH($E20, PhotoNums, 0)):INDEX(QTY, MATCH($E20, PhotoNums, 1)))

This is a dynamic range formula, too, grabbing all the values in the QTY rng that match value selected in the PhotoNums range.

I use this technique with great success in xl2003, I know there is difficulty with getting it turned on in xl2007. Since the file you sent is xl2007 (your profile says xl2003) then this will work since I already have it turned on for you, you will experience trouble editing it.

[Hlowmaster]

Thanks a lot sir