Limit data validation values to a 1 point margin in 3 different cells

**purdue7997** · 11-17-2009, 11:58 AM

I have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)

Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.

Summary: Display an error message if any of the points are more than 1.0 point from another.

Thanks, you guys have always been great at hepling me!

**Palmetto** · 11-17-2009, 01:20 PM

It would be easier to answer if you post a sample workbook showing which cell containing validation are to be compared.

**purdue7997** · 11-17-2009, 01:32 PM

Here is the sheet that has my data validation problem on it. Also, if there is a solution to it, could it be done without macros? If not I'll take a macro solution. Thanks!!!!!!

**DonkeyOte** · 11-17-2009, 02:28 PM

Based on your sample file, without wanting to alter J52:J60 (unknown knock-ons) you could perhaps do something like

Please Login or Register  to view this content.

Then highlight M12:M14, selecting M12 first, and apply a Validation List of:

Please Login or Register  to view this content.

Note: I wasn't entirely clear on the rules and have assumed that when first value is entered, say 9 the next value could range from 8-10, if 8.5 is selected the 3rd selection options can only fluctuate from 7.5 to 9.5
(ie the +/- is based on the MIN)

**purdue7997** · 11-17-2009, 03:31 PM

You hit the nail on the head! You guys always amaze me. Thanks and I'll add to your rep!

**Palmetto** · 11-17-2009, 03:48 PM

Post late, but a similar solution to DO, only w/o the helper column.
I assume you list of numbers will remain fixed, but you could name the range so Excel will track it if you move it.

Data validation formula, list option:
=OFFSET(INDIRECT(ADDRESS(MATCH(MIN($M$12:$M$14),$J$52:$J$60,0)+51,10)),,,3,1)

Edit: in testing the formula by DO, you can get a spread of points greater than 1-point. Based on the OP's note in the workbook the total spread from lowest score to the highest should not be greater than 1-point.

**DonkeyOte** · 11-17-2009, 03:57 PM

Edit: in testing the formula by DO, you can get a spread of points greater than 1-point. Based on the OP's note in the workbook the total spread from lowest score to the highest should not be greater than 1-point.

Agreed, though in defence I made that point myself...