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

[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

 

[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

 

[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

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

 

[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

 

[Joe]

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

 

[Joe]

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

 

[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

 

[Joe]

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

 

[Pete_UK]

Thanks for the feedback.

Pete