Excel drop-down validation lists and explanatory notes

Question

Hi,

I'm trying to design an excel spreadsheet where cells in various columns are populated by choices from a drop-down validation list. The options in the validation list are (for example) 1, 2, 3 and 4; where 1 = excellent, 2 = very good, 3 = good and 4 = not great. I want to be able to sum across columns so it's important that only the numeric values are included in each cell. However, I also need to include information so that a user knows that selecting '1' means that the item was 'excellent'. I have seen this done by way of a drop-down list for the cell values and a type of text box that appears when a cell is selected. But I cannot figure out how they did it. See the screenshot below for an example of what I mean.

Can anyone tell me how to do this?

Many thanks, Lesley

Answer 1

Hi Lesley,

See Debra Dalgleish's Data Validation -- Display Input Messages in a Text Boxpage at:

http://www.contextures.com/xlDataVal12.html

===

Regards,

Norman

Answer 2

I set up my data validation range to include the description.

Something like:

                         01 Good

                         02 Satisfactory

                         03 Poor

                         04 Not trying

the "xx description" would all be in one cell.

Then, to sum it up, I would use a formula that reads the first two characters of the validation cell and, with the value function, convert the choice to a number that can be used arithmatically in a formula.

To make things look nice, I usually pad the choices with leading zeroes if the range is greater than 10 (or 2 leading zeroes for ranges greater than 100...) and adjust how many leading characters to read.

Answer 3

Hi Norman, thanks so much for your email. This was exactly what I was after. I also discovered (via the Contextures website) a simpler solution to my query. I only needed to look a little further within the Data Validation dialog box and would have found the 'Input Message' tab where I could type exactly the instructions I needed. This option works for me because my instructions have few characters and so I'm not restricted by text box size and formatting. The link you forwarded goes a step further where more space and the ability to format is required.

Thanks again, I really appreciate your help.

Lesley

Answer 4

Hi Raokman, thanks heaps for your reply. I didn't try out your method since Norman's response sorted things for me, but perhaps my reply to Norman may assist you.

Regards and thanks again,

Lesley

Answer 5

Hi Norman, thanks so much for your email. This was exactly what I was after. I also discovered (via the Contextures website) a simpler solution to my query. I only needed to look a little further within the Data Validation dialog box and would have found the 'Input Message' tab where I could type exactly the instructions I needed. This option works for me because my instructions have few characters and so I'm not restricted by text box size and formatting. The link you forwarded goes a step further where more space and the ability to format is required.

Thanks again, I really appreciate your help.

Hi Lesley,

Thank you for your kind feedback  which is much appreciated,

However, reading this in conjunction with your reply to Raokman, I suspect that you have inadvertently inverted your selection of which response to mark as Answer!

Thank you again.

===

Regards,

Norman