Drop Down Menu HELP ...please

[Jaxjag96]

Hello .... I'm an old Excel user FINALLY unleashing the "full potential" of EXCEL after 10+years..... I'm trying to create a set of drop down menus for a company estimating program. Yes...drop downs are easy, but getting another drop down to actually "change" when a cell value is changed has proven most difficult for me.

I've tried the basics, all the way up to a VLOOKUP for the drop down lists. I have attached a sample ZIP file with my dilemma, as it's pretty self explanatory. I have a list of data (A,B,C,D,E) - each that corresponds to another list of data (A - 1,2,3,4,5 ; B - 11,22,33,44,55 ; C - 111,222,333,444,555 ; etc......) I want to be able to select from the first list (say A1), and then have the corresponding secondary list (say A2) come up depending on the value of A1.

Is this possible....i've always heard ANYTHING is possible in EXCEL....

Please help! Thank you for taking the time!

Chris - Unique Landscapes
Phoenix, AZ

[Maistrye]

I attached it with the simple solution. I modified your AZ Block in the dropdown list to read AZBlock. You could also go AZ_Block and still have it work fine. Basically, the name of the destination range has to match the name in the list.

If you need spaces or want to use alternative schemes (such as number drop downs), other may or will be necessary. (When you get to something like that, you can ask again... no need to overwhelm you all at once)

I added some Conditional Formatting... the colour I chose might not be good for you. :-)

Scott

[Jaxjag96]

Thanks for the help - i understand exactly what you have done, i'm so glad you showed me that - I found something online using =OFFSET(INDIRECT($B2),0,0,COUNTA(....etcetc.....

it worked the same way, but your way is MUCH more simple.... You have just saved me HOURS of work - THANKS!

BY the way, i was trying to figure out how you did the conditional formatting - should i just copy your formula, and input the cells (when i transfer this to my master files?) I followed you all the way up to the conditional formatting, then i lost ya....but i'll practice some trial and errors on that part of it.

Thanks again!

[VBA Noob]

Hi,

Yes you can copy the formula to your sheet. Just chang the cell references if required

=IF(AND(TRIM(C8<>""),ISERROR(MATCH(C8,INDIRECT(A8),0))),TRUE,FALSE)

A8 = Patio drop Down
C8 = Result

The attached has more info

http://www.contextures.com/xlCondFormat01.

VBA Noob