Impossible pull down list problem

[plost33]

If someone can assist me with this I will be so happy!!

Here is the situation: A two sheet spreadsheet. Sheet one is a form and sheet 2 is a hidden page that contains the list and named ranges for all the pull down lists on sheet 1. On sheet 1 there is a colunm that contains a pull downlist, it works fine. The column next to it contains cells which have pull down list that are dynamic based upon the selection made in the first pull down list. I did this by using the indirect(...) function in the source space in the data validation box. This works fine.

The problem is: I want the selection made in pull down 2 to change once a selection has been made to show a diffrent value. For example: If pull down 2 contained a list of all 50 states by full name and the user selects Texas I want the data shown in the box to be the abbreviation for Texas, TX, rather than the actual word "Texas".

I have the data on sheet 2 and can easily make this work using a simple vlookup, but I cannot seem to get the vlookup to work nested inside the indirect function in the source section of the data validation creation box.

I am hoping someone may assist.

I do know that I could add a column in and have the vlookup function work, but I want it all to be done within one cell. Please assist....
<!-- / message -->

<!-- controls -->

 

[gtr_chennai]

i think u can get solution to write macro with worksheet_change function. take a target address and match with select case then u can display the value which u want.

 

[plost33]

thanks for the help. I have limited experience with writing a macro. Can you help me? I want the value in cell D12 to change automatically when I put in certain values. If I type in "Weather" I want the cell to change to say "WOW". If I type in "Bell Run" I want it to change to say "BR".

Thanks ...

 

[Andrew Poulsom]

Here is an example:

http://www.mrexcel.com/forum/showthread.php?p=1626775

you would need to use the VLookup worksheet function to find the replacement test. If you don't know how to do that tell us where your lookup table is.