Excel cells- show displayed text instead of value in Data Validation list

microsoft excelmicrosoft-excel-2007

I read this topic: Excel cells – show text different to the actual value?, and now I'm trying to use it as a Data Validation list in Excel.
But when I choose for the list those cells, when I choose one of them it shows me the value data and not the display text (I'm using Microsoft 2007).

Here is an example:

Instead of showing "Thursday" it shows me in the cell the value of Thursday: "5"

How can I get the chosen value of the list to show the display text and not the cell value?

EDIT: Please take the days of week just as an easy example, the real question is how to do that when the values are some other text (Like: 1=Dog, 2=Cat"…..)

Best Answer

Let's say I want dog;cat;mountain lion;armadillo... etc to appear when I type 1;2;3;4... etc.

We can use conditional formatting to create a rule for each possibility.

Create a new rule. Select a rule type of Format only cells that contain, set the value to 3 for mountain lion, then click the format button.

In the format cells box, set the custom number format to "mountain lion". Make sure to enter quotes around the text you're using so Excel understands this is text and not really a number at all.

enter image description here

Related Solutions

Excel cells – show text different to the actual value

You can indeed use conditional formatting, resonably simply:

Apply conditional format to one sample cell (using cell A1 as the example)
Create a set of 7 format conditions,

Condition formula       Format (Number: Custom)
=A1=1                   "Sunday"
=A1=2                   "Monday"
=A1=3                   "Tuesday"
=A1=4                   "Wednesday"
=A1=5                   "Thursday"
=A1=6                   "Friday"
=A1=7                   "Saturday"

Then copy paste special format (or use the format painter) to apply to other cells as required

Excel – Select Multiple Items from Excel Data Validation List

I was looking to do the same and found these great resources. The video and file download are specific to Excel 2003 but worked with 2010 ...should work with 2007.

Select Multiple items from validation list - http://www.youtube.com/watch?v=0rH8N-Puvbo Unless you want to rewrite the macro, you'll need the sample file download - http://www.contextures.com/excelfiles.html#DataVal

I downloaded DataValMultiSelect sample file DV0017 and used the LineBreak macro to separated each item by a line break. I will simply hide what I'd call the "working column" which shows only the last value picked.

Note: I had no problem with the fiile download but will warn, just for the record: you should not download macro-enabled files unless you have antivirus software running with the lastest update and trust the source.

Best Answer

Related Solutions

Excel cells – show text different to the actual value

Excel – Select Multiple Items from Excel Data Validation List

Related Question