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



On Feb 29, 6:28 pm, "Greg Allen" <gregory.al...@sierraatlantic.com>
wrote:
> 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

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


"Tim879" <(E-Mail Removed)> wrote in message
news:71aee93c-5b7b-42f9-88e9-(E-Mail Removed)...
> 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
>
>
>
> On Feb 29, 6:28 pm, "Greg Allen" <gregory.al...@sierraatlantic.com>
> wrote:
>> 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
>

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

Greg Allen wrote:
> 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
>
>
> "Tim879" <(E-Mail Removed)> wrote in message
> news:71aee93c-5b7b-42f9-88e9-(E-Mail Removed)...
>
>>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
>>
>>
>>
>>On Feb 29, 6:28 pm, "Greg Allen" <gregory.al...@sierraatlantic.com>
>>wrote:
>>
>>>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
>>
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html