Data Validation - Custom

[Razoo]

Excel 2003

Please can someone tell me the simplest formula that I can put in 'Data
Validation/Settings/Custom/Formula:' to limit a range of cells to being a
number between -24 and 24?

At the moment I have:

=AND(ISNUMBER(A1),SUM($A$1:$A$21,$B$1:$B$21)<=24)

Now I see a need to limit the negative number as well.

Thanks,

 

[Razoo]

A simple way:
In a range of otherwise unused cells (could be hidden or on
another worksheet), enter -24, -23, -22,.....20,21,22,23,24
Give this range a name (mylist)
In the Data Validation dialog, specify List with Source set to
=mylist (you need the =)

Hi Bernard,

Thanks for the advice. Naming a range of cells and using them as a list for
Data Validation is not something I knew about.

However, I don't think this method will work for me as data in the range of
cells I'm trying to validate can be entered to two decimal places. I should
have mentioned that in my first post.

Although the number entered has to be between -24 & 24, -10.5 or 14.75
would both be valid entries.

I assume I will have to use both >=-24 and <=24 in the custom formula,
unless there is some way of specifying a number range in a single argument.

Regards,

 

[Bernard Liengme]

From Help in Excel 2007 but Excel 2003 works the same way:

Restrict data entry to a decimal number within limits
Select one or more cells to validate.
On the Data tab, in the Data Tools group, click Data Validation.

In the Data Validation dialog box, click the Settings tab.
In the Allow box, select Decimal.
In the Data box, select the type of restriction that you want. For example,
to set upper and lower limits, select between.
Enter the minimum, maximum, or specific value to allow. You can also enter a
formula that returns a number value.


best wishes

 

[Razoo]

From Help in Excel 2007 but Excel 2003 works the same way:

Restrict data entry to a decimal number within limits
Select one or more cells to validate.
On the Data tab, in the Data Tools group, click Data Validation.

In the Data Validation dialog box, click the Settings tab.
In the Allow box, select Decimal.
In the Data box, select the type of restriction that you want. For
example, to set upper and lower limits, select between.
Enter the minimum, maximum, or specific value to allow. You can also enter
a formula that returns a number value.

Hi Bernard,

Thanks for the Data Validation help.

Unfortunately I think I'm going to need spoon-feeding on this one :-(

I followed your instructions, highlighted the range of cells A1:B21 and
selected Data Validation / Settings / Allow: Decimal / Data: Between.

However, I'm not sure how to write the formula for the 'Minimum' and
'Maximum' fields.

It is the sum of the data in cells A1:B21 that has to be between -24 and 24.

How do I write the formula so that the sum of cells A1:B21 is checked
against the limits -24 to 24?

I've tried a few variations of:

=SUM(A1:B21).... but the formula doesn't give the correct results.

I assume I understood you correctly that formulae can be entered in the
'Minimum' and 'Maximum' fields? Or were you referring to the 'Custom'
formula field?

Regards,

 

[Bernard Liengme]

There is no way Data Validation can do that. Suppose I fill each cell in
A1:A21 with -1 and each cell in B1:B20 with 1.
SUM(A1:B21) now equals -1. I think I can contrive a way to have data
validation make sure B21 has a valid value but if you are talking about
entering values in random cell sin the range, we are out of luck

..However, here is a suggestion. We could give A1:B21 a conditional format
such that when the sum falls outside the range, the entire range is shaded
red.
Select A1:B1 in Excel 2003 use Format | Conditional Formatting (in Excel
2007 look in the Styles group of the Home tab) and use Formula Is
=OR(SUM($A$1:$B$21)<=-24,SUM($A$1:$B$21)>=24) and set the format to a red
fill

Alternatively enter =SUM(A1:B21) in some cell and hace jus that turn red
with the values lies outside the range
best wishes

 

[anandydr]

In Excel 2007 Go to data validation>Highlight Cell Rules>More rules...
In the dialog box that appears see "Format only cells with" leave drop
down box with cells with in second drop down select "not between" in
the cells you can specify -24 and 24. Select format you want with the
button below. Choose a fill color that you can easily identify.
On my computer this works with decimal places too....

Hope that helps
Anand Kumar
09910548139

 

[Razoo]

In Excel 2007 Go to data validation>Highlight Cell Rules>More
rules...
In the dialog box that appears see "Format only cells with" leave
drop down box with cells with in second drop down select "not
between" in the cells you can specify -24 and 24. Select format
you want with the button below. Choose a fill color that you can
easily identify. On my computer this works with decimal places too....

Hi,

Thanks for the information.

Unfortunately at the moment both my work computer and my home computer are
running Excel 2003.

I'll try your suggestion as soon as we get MS Office upgraded!

Regards,

 

[Razoo]

There is no way Data Validation can do that. Suppose I fill each cell in
A1:A21 with -1 and each cell in B1:B20 with 1.
SUM(A1:B21) now equals -1. I think I can contrive a way to have data
validation make sure B21 has a valid value but if you are talking about
entering values in random cell sin the range, we are out of luck

.However, here is a suggestion. We could give A1:B21 a conditional format
such that when the sum falls outside the range, the entire range is shaded
red.
Select A1:B1 in Excel 2003 use Format | Conditional Formatting (in Excel
2007 look in the Styles group of the Home tab) and use Formula Is
=OR(SUM($A$1:$B$21)<=-24,SUM($A$1:$B$21)>=24) and set the format to a red
fill

Alternatively enter =SUM(A1:B21) in some cell and have just that turn red
with the values lies outside the range

Thanks Bernard,

I guess I must return to where I came in:

Select Data Validation / Settings / Allow: Custom

Then set :

=AND(ISNUMBER(A1),SUM($A$1:$B$21)<=24,SUM($A$1:$B$21)>=-24)

in the formula field.

I've put an appropriate message in 'Error Alert' and it seems to work OK.