Macro: From Dropdown text containing value, nest line result another value

[Rober]

Hello,
I have this example attached.
In column A1:A7 I have some drop down Data Validation lists and they are all the same.
I would like to have a macro so that when I enter a choice from the list, the next line will be filled out automatically with a value from the list with another value, always from the same list.
Ex. If a line has a text containing 500A the very next line will be filled with another value from the list i.e. text containing 600B.
If a line has a text containing 500B the very next line will be the one with text containing 600D.
I have only lines 500A and 500B that need this second line value....

Sorry for my English.....I realize it sounds weird!!!!!

Thanks
R

[wallyeye]

You can use a bit of VBA for this. Go to the VBA IDE (alt-F11) and put this code in the module of the worksheet where you want to auto-populate the cells:

Please Login or Register  to view this content.

Then, on another worksheet, build a map like this:

Text containing 500A Text containing 600B
Text containing 500B Text containing 600D

and give it a name of NestMap.

When you change the target worksheet within the range of A2:A8, it will trigger the code to look for the value in the NamedRange with the map. If a match is found, it will change the row immediately below to the mapped value.

BTW, I would recommend you use a named range for your data validation map as well, over time it will work better than typing the validation entries directly into your data validation.