NEED : Multiple conditions, drop down and return a numeric value - HELP!

Dear Excel-perts:
I am desperately trying to do the following and need advice:
In a cell, I need to2. Then, in choosing one of the 5 options, it needs to return a numerical value 1-5, rather than the entire option text;
3. Then, when returning the appropriate value, I want the cell to fill with a different color based on a simple chart:
1 = red fill
2 = yellow fill
3 = green fill
4 = blue fill
5 = purple fill

Does anyone know how I code this multi-function formula?
Many thanks!!!!
Lynne

Hello
Take a look at my attachment. Is that what your looking for?

Thanks Hercules, but the attachment didn't have any coding or format. Can you resend?

LynneB said:

Thanks Hercules, but the attachment didn't have any coding or format. Can you resend?

Click to expand...

Helllo Lynne
It seems to work OK to me. If you click the drop down to cell A1 and select one of the text entries, cell A1 should change to the appropriate colour.
Let me know if that doesn't work.

Hi Lynne,
pl find attachment. is it help you ?

Hi LynneB,

Have a look at this file. The drop down is in cell A1. As soon as you choose the option, it is converted into the corresponding number (using custom number formatting). And then Conditional Formatting does the color fill.

View attachment Custom and Conditional Formatting.xlsx

sumitbansal said:

Hi LynneB,

Have a look at this file. The drop down is in cell A1. As soon as you choose the option, it is converted into the corresponding number (using custom number formatting). And then Conditional Formatting does the color fill.

View attachment 2964

Click to expand...

Sumit
I think lynne has found an answer somewhere else. Can you explain how you get the numbers into A1 please ? I can see the custom format text entries, but not how the numbers are linked to them?

Thanks

Hello Hercules,

Try this:

- Type 0 in a cell
- Press Control + 1 and select custom format
- In custom format field, type "No Experience"

Now this cell will have the value 0, but show "No Experience"

Now if you use this cell in data validation, it will show No Experience, but will hold the value 0.

I have an article on my website (since I am not allowed to paste the url link till I make 5 posts, pardon my weird way of explaining the url)

Go to TrumpExcel . Com and search for the word disguise. The first article that you get will show a detail way of doing this.

Sumit
Thank you. I wasn't aware of that.