Dependent Validation List (Single)

I have searched the forum for a few hours to find a solution with no success. I have a list with reference name "POINTS" that lists values from 1-35. Users have the option of evaluating a separate list of criteria based on level of importance from 1-35. There are 35 criteria and each requires a different value. I have copied the validation list to each cell opposing the list for easy selection.

What I would like is for when a value from the list is selected for one item, it is no longer available for selection for the other criteria. In this way no value can be selected twice.

Is there an easy way to do this? I'm using Excel 2010.

Thanks.

I'm not seeing how that will help. Let me illustrate it:

Criteria Importance

Criteria 1
Criteria 2
Criteria 3
Criteria 4
Criteria 5

The criteria are in column A with "Criteria 1" in A5. Importance selection is in B5 with a validated list, "POINTS", which references the values 1-5, from another location in the worksheet. Right now I have the same validated pull down list in C5, D5, E5, & F5. This allows the user to select from the pull-down list the value they want to assign to the criteria. What I would like, is when they select "1", for example in B5 for Criteria 1, it will not be an option for C5, D5, E5 or F5. Only 2-5 would then be available.

Forgive me if I'm missing something but I don't see how the COUNTIF function will assist me here? I tried using "Custom" in the validation but it doesn't give me a pull down list, which is a must for this sheet.

Thanks for your time. I really appreciate it.

Validation Example.xlsx

I hope I did this right?

Thanks. Jonathan

Chris,

I apologize. I had the wrong refernce in the first file. This one is correct. Please ignore the last post.

Validation Example-REV1.xlsx

check out this file and see if it will work for you

When I enter the formulas from the Revised sheet, all I get are number values.

My selection sheet is Sheet 3, while all the formulas are on Sheet 2. I copied the formula you had for 'NameCheck' into my list "IMPORTANCE"

=OFFSET('Sheet 2'!$C$1,0,0,COUNTA('Sheet 2'!$C$1:$C$6)-COUNTBLANK('Sheet 2'!$C$1:$C$6),1)

I don't know what I'm doing wrong as t's not letting me have a pull down list?

Validation Example-REV2.xlsx

not sure what went wrong --- I retyped in the formula in your named range and it worked fine

It's working now. I simply renamed Referred "IMPORTANCE" to C1:C5 on Sheet2 (instead of the OFFSET formula in your example) and it worked fine!

Thanks you so much for your example! I will be able to use this many times. Really appreciated!

glad you got it to work