Multiple Data Validation in one cell

**willmark** · 12-07-2012, 02:40 PM

Good afternoon,

I am a teacher trying to figure out how to enter marks as both numerical values (e.g., 89%) and levels (e.g., A-).

Currently, my "markbook" has a worksheet on which I enter all of the students' marks and then excel transfers automatically those marks to the students' individual pages where the numbers are crunched and a final mark is calculated.

My school is moving to a system of marking in LEVELS. Just to complicate things (for Excel), these levels - which go from R (<50) to 5+ (98-100%) - are numeric levels. I know, though, that I can set the levels to be read as text.

I would like to set up my data entry worksheet so that I can indicate whether or not I will be marking in numbers or levels and then be able to enter the students number/level in one cell. I am trying to set up that cell to be able to recognize that the value I am entering will be a level (with data validation from a list) or a number.

So far, I have come up with the following formula for the Data Validation Window --> =IF($O$4,Levels,???????)

In cell O4, I have created a check box to indicate whether the assignment will be marked in levels or numbers. If the check box is checked, then the formula tells excel to create a drop-down list from a range I have labelled "Levels". This range includes a blank, R, 1-,1,1+,2-,2,2+,3-,3,3+,4-,4,4+,5-,5,5+.

My problem is that I do not know what to put at the end of this IF statement. I want to be able to enter any number I want (with decimals), from 0-200...and I would prefer not to create another range with each of the possibilities between 0 and 200.

Any help would be greatly appreciated. I am trying to do this without VBA, because it is far beyond my knowledge set.

Thank you, in advance.

**Ron Coderre** · 12-07-2012, 03:21 PM

Perhaps you could take advantage of Data Validation's error checking features:

Try this
• Put your list of levels in a named range...I used: LU_Level
• Select the cells to be impacted with the DV
• CTRL+1....(to open the formatting window)
...Number_tab
...Category: Text
...Click:OK
• Data.Data_Validation.Data_Validation
...Allow: List
...Source: LU_Levels
...Error_Alert_tab
...Style: Information
...UNcheck: Show error alert
...Click: OK

Now...Each cell will display a dropdown list of the levels...BUT...you can also enter anything you want.
If that works for you, we'll need to figure out how to summarize/average/whatever the resulting values.
You may need to amend your list to something like:
L -1
L 1
L 1+
etc

**willmark** · 12-07-2012, 04:10 PM

Levels and Numbers - Forum.xlsx

Hi Ron,
Thank you for the quick reply. I confess that I did not follow your instructions once I tried CTRL+1. This did open a formatting window, but the standard one (Number, Alignment, Font, etc). I am Excel 2010, but I don't think that is the issue.

I am attaching a copy of a working version of the spreadsheet that I am trying to create. I hope that might help.

Thank you.

Mark

**willmark** · 12-07-2012, 04:14 PM

Hi Ron,

My mistake - I misread one of your instructions. I have made the changes that you suggested.

I am going to work through this to see how it goes.

THANK YOU!

Mark