Combining validation types

  • Thread starter Thread starter Greg Allen
  • Start date Start date
G

Greg Allen

Is there a way to combine validation types on a cell?

What I would like to do is force the cell value to be either a whole number
or a selection from a list.

Is that possible?

Thanks,

-- Greg
 
Try this...

Create a custom list somewhere in your spreadsheet for the data
validation to refer to. I did mine in cells F1:F7. For the cell that I
want to validate (a1 in my example), I used the round function to
round that cell to the nearest whole number (i.e. in cell F1, I had
the function =round(a1,0)). F2:F7 had the rest of my list.

In cell A1 in my sample sheet, I set the data validation to refer to a
list in the range F1:F7.

When I enter a decimal number in A1, the validation correctly fails
however if you enter any whole number or a number in the list, the
validation will pass. Note the validation for the decimal fails
because you are comparing the rounded (whole) number in cell F1 to the
number with decimal in cell A1. If you need to do this check over a
longer range, just add more round functions to your list.

There may be an easier way but this works and only took a few seconds
to set up.
Good luck
Tim
 
That will work for a particular cell. Thanks!

Is there a way to modify this so that I can use the same range of cells
as validation for many other cells? Say I wanted to validate every
cell of column A using F1:F7. I don't want to have to create a different
validation range for each cell I need to validate...

Thanks,

-- Greg
 
This method won't create a dropdown list of choices, but will limit the
entries to those items in the list, and whole numbers:

Select the cells where you want the validation, in this example cells
A1:A100, with A1 as the active cell.
Choose Data>Validation
For Allow, select Custom
In the Formula box, enter:
=OR(COUNTIF($F$1:$F$7,A1),IF(ISNUMBER(A1),A1=INT(A1)))
Click OK
 
Back
Top