Drop Down List Question

Is it possible to create a list,

For example:
Susan
Jeffrey
Thomas

Use the named list to create a drop-down list on a Worksheet (using Data Validation) and when a selection is made from the drop-down list , see different (associated) data, IN THE SAME CELL.

For example:
Select "Susan" but get "Sue"
Select "Jeffrey" but get "Jeff"
Select "Thomas" but get "Tom"

I thought about using the Conditional Formatter but that doesn't look like there is an option there for me. Perhaps I need some VBA logic to detect the value in a Cell and then change it (behind the scene).

What do you think?

If you are using a data validation drop-down, then you are trying to ensure that only valid entries are made, i.e. those within the named range. That means that you cannot have Sue, Jeff or Tom in that cell, as those are invalid entries.

You would need to use a different cell to display the abbreviated names in, possibly using a formula with VLOOKUP to get the shorter names from a table.

Hope this helps.

Pete

Thank you, Pete. Yes, that helps, in that now I know that it's not really possible to do what I was looking for using the Data Validation.

Sue

Thanks for feeding back, Sue.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thank you, mehmetcik. Your suggestion was just the ticket I needed.

I, actually, decided to put the function in Workbook_SheetChange( ). This is because the workbook has many sheets, all with the same column holding the same type of data. In my case the value is a "Status". So, rather than update EVERY WorkSheet_Change( ) function, I thought it would be a better idea for me to just add 1 function and modify it to work for me.

I did run into a problem, however. When selecting a value from the drop-down list, the Workbook_SheetChange( ) function is called. Great. But when the value is changed by selecting a value in the array the function is called again, recursively, from within the function, itself. This caused the logic catching a value not expected. I was able to get around this problem by creating a global variable that would tell the function if it is the first or second time into the function and take action accordingly.

Thanks, again. This was a fun little problem for me to solve and you helped.

Here's the code that I used.

Oh, btw, I have another column that I am doing the same thing with so you will see logic for that, as well. And I change the cell background color, as well, just for cosmetic purposes.

Oh, last thing ... you will notice that I am doing (mostly) the same thing for two different columns. Normally, I would create a called function to do that work. BUT, I tried and tried and just could not figure out how to pass Target. So, sadly, I gave up. Maybe I'll revisit this when I get a moment.

Please Login or Register  to view this content.

I'm not sure if my previous reply was sent so ...
Thanks, again. This additional suggestion looks great. I'll give it a go. Thank you.