Need more complex data validation than I can see available from themenu


J

Joe

I have an excel 2003 spreadsheet, and I want the data in cell B to be
between 2 values if the data in cell A is between 2 other values. For
example, cell A contains an annual review score. Cell B is to contain a
merit increase percentage. Once the user has entered a score in cell A
(say "2") and then enters a percentage in cell B (say "3.5") I want the
validation to check if cell A is between 1 and 2, don't let the merit
percent be over 3.0 or under 1.0).

Any help would be appreciated! I'm scrounging through the help files and
an ebook of macro coding, but I can't come up with the correct approach.

Thanks,

Joe
 
Ad

Advertisements

R

Ron Rosenfeld

I have an excel 2003 spreadsheet, and I want the data in cell B to be
between 2 values if the data in cell A is between 2 other values. For
example, cell A contains an annual review score. Cell B is to contain a
merit increase percentage. Once the user has entered a score in cell A
(say "2") and then enters a percentage in cell B (say "3.5") I want the
validation to check if cell A is between 1 and 2, don't let the merit
percent be over 3.0 or under 1.0).

Any help would be appreciated! I'm scrounging through the help files and
an ebook of macro coding, but I can't come up with the correct approach.

Thanks,

Joe
You can do it with a formula that returns TRUE or FALSE depending on whether
the conditions are met.

For example, for just what you have written:

=IF(AND(A1<=2,A1>=1,B1>=1,B1<=3),TRUE,FALSE)

This will also result in an error if A1>2 or A1<0, when you try to put anything
in B1. How to expand this depends on what you want to have happen in the
instances you have not defined in your post.
--ron
 
J

Joe

Ron said:
You can do it with a formula that returns TRUE or FALSE depending on whether
the conditions are met.

For example, for just what you have written:

=IF(AND(A1<=2,A1>=1,B1>=1,B1<=3),TRUE,FALSE)

This will also result in an error if A1>2 or A1<0, when you try to put anything
in B1. How to expand this depends on what you want to have happen in the
instances you have not defined in your post.
--ron
Thanks, Ron.

I'm still doing something wrong -- I want the data to validate as such:

Annual Eval Merit Increase
1 = 0%
2 = 1% - 2%
3 = 2.5% - 4%
4 = 4.5% - 6%

I tried each of the following:
=IF(AND(H6=1,I6=0,I6>0)*AND(H6=2,I6>=0.01,I6<=0.02)*AND(H6=3,I6>=0.025,I6<=0.04)*AND(H6=4,I6>=0.045,I6<=0.06),TRUE,FALSE)

=IF(AND(H6=1,I6=0,I6>0)*OR(H6=2,I6>=0.01,I6<=0.02)*OR(H6=3,I6>=0.025,I6<=0.04)*OR(H6=4,I6>=0.045,I6<=0.06),TRUE,FALSE)

With either formula, I always get FALSE regardless of the combination
entered.

TIA,

Joe
 
P

Pete_UK

Joe,

try this:

=IF(OR(AND(H6=1,I6=0,I6>0),AND(H6=2,I6>=0.01,I6<=0.02),AND(H6=3,I6>=0.025,I6<=­0.04),AND(H6=4,I6>=0.045,I6<=0.06)),TRUE,FALSE)

Hope this helps.

Pete
 
J

Joe

Joe said:
Thanks, Ron.

I'm still doing something wrong -- I want the data to validate as such:

Annual Eval Merit Increase
1 = 0%
2 = 1% - 2%
3 = 2.5% - 4%
4 = 4.5% - 6%

I tried each of the following:
=IF(AND(H6=1,I6=0,I6>0)*AND(H6=2,I6>=0.01,I6<=0.02)*AND(H6=3,I6>=0.025,I6<=0.04)*AND(H6=4,I6>=0.045,I6<=0.06),TRUE,FALSE)


=IF(AND(H6=1,I6=0,I6>0)*OR(H6=2,I6>=0.01,I6<=0.02)*OR(H6=3,I6>=0.025,I6<=0.04)*OR(H6=4,I6>=0.045,I6<=0.06),TRUE,FALSE)


With either formula, I always get FALSE regardless of the combination
entered.

TIA,

Joe
Update - I got it to work, by creating a couple of lists and naming the
ranges and then using them with data validation. I had gotten this site
http://www.contextures.com/xlDataVal13.html as a reference point, and it
gave me the ability to 'force' a range of merit % to an entered annual
evaluation score.

If I may build on this question, though -- Is there any way to not allow
the user to disregard the drop down arrow and enter their own value? I
thought of using a formula as I list above (=IF(AND(etc)but it returns
FALSE even if the entries match the table I listed above.

TIA,

Joe
 
J

Joe

Pete_UK said:
Joe,

try this:

=IF(OR(AND(H6=1,I6=0,I6>0),AND(H6=2,I6>=0.01,I6<=0.02),AND(H6=3,I6>=0.025,I6<=­0.04),AND(H6=4,I6>=0.045,I6<=0.06)),TRUE,FALSE)

Hope this helps.

Pete
Pete - thanks, that almost gets me there.

It returns True and False correctly except for when H6=1 and I6=0.0%
----- it should return True for this combination, but it returns False.

Is the problem that the I6 cell is formatted for percent?!?

TIA (this group rocks!)

Joe
 
P

Pete_UK

Joe emailed me directly with his posting, so I replied thus:

Hi Joe,

I copied the formula from what you had posted and just re-arranged it
without looking too closely at it. The first AND can never be true, so
you
need to amend the formula as follows:

=IF(OR(AND(H6=1,I6>=0),AND(H6=2,I6>=0.01,I6<=0.02),AND(H6=3,I6>=0.025,I6<=­0.04),AND(H6=4,I6>=0.045,I6<=0.06)),TRUE,FALSE)

Hope this helps.

Pete
 
J

Joe

Pete_UK said:
Joe emailed me directly with his posting, so I replied thus:

Hi Joe,

I copied the formula from what you had posted and just re-arranged it
without looking too closely at it. The first AND can never be true, so
you
need to amend the formula as follows:

=IF(OR(AND(H6=1,I6>=0),AND(H6=2,I6>=0.01,I6<=0.02),AND(H6=3,I6>=0.025,I6<=­0.04),AND(H6=4,I6>=0.045,I6<=0.06)),TRUE,FALSE)

Hope this helps.

Pete
Pete,

That does what I need!

Thanks for the help,

Joe
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top