Is it possible to show a different value after selecting data the validation list?

Hello,

Could someone clarify if it is possible to show a different value in the same cell after selecting an option from the drop down / validation list?

Data validation list:
Cell A1: John - Perform customizations
Cell A2: Rosh - Consolidate activities
Cell A3: Ruth - Monitor implementation

If a user selects the first one from the list, I want cell A1 to show a custom value - just the name "John."

In other words, override cell A1 to show only "John" even though the selected option is "John - Perform customizations."

Another example: Data validation list
5
4
3

Expected value in the cell:
High
Medium
Low

Please let me know if the above example is not clear. I would very much appreciate some suggestions.

Best,
Krish

Originally Posted by 6StringJazzer

What you are asking to do is a contradiction. Data validation requires that the cell contains only the values in the list, and you are asking for it to contain a value that is not in the list. You could do this with VBA but only if you remove the data validation restriction before updating the content of the cell. Something like this could be done using a multicolumn combobox linked to a cell. See example.

Hi, thanks for taking the time to reply with an example. I agree that it is counterintuitive to use the data validation list and replace it with custom values. The goal here is to provide a detailed view to the end user in the drop-down, instead of adding comments, but at the same time time, show only what is relevant for calculation/reporting. Do you have any other suggestions?

Here's an example with vba:
How to setup:
1. Create a named range “xDVAL”, Refers to: =Sheet1!$C$2:INDEX(Sheet1!$C:$C, COUNTA(Sheet1!$C:$C), 1)
2. Create a data validation in A2:A10, choose “Allow > List” then source: =xDVAL
3. On the Error Alert tab, uncheck mark on "Show error alert after invalid data is entered"
4. Put this code in sheet1 code module:

Please Login or Register  to view this content.

just for info...

where the "mask" is text and the underlying value is numeric you can do this using custom formats but, it is not advised as it's not very practical !

e.g.:
D1: 5 -- set custom number format to "High"
D2: 4 -- set custom number format to "Medium"
D3: 3 -- set custom number format to "Low"

then, A1 DV list =D1:D3

when you activate the DV list in A1 you will see the "masked" values (High, Medium & Low) but, when you choose a value you will have the underlying value returned to the cell (i.e. 5, 4 or 3)

Originally Posted by 6StringJazzer

I had considered the solution offered by Akuini, but I assumed that an attempt to assign a value that was not in the DV list would fail. Apparently it does not. Nice job.

Thanks for examining my code & also for rep+.