Data Validation based on range of values

[jej1216]

I have cell A which is for Annual Evaluation Score, and the user enters
between 0.0 and 4.0. Then I have cell B for Merit Increase Percentage,
and the user can enter between 0.0 and 5.0.
I need to validate the entry in cell B against the value entered in
cell A --- if cell A is between 0.0 and 1.0, the value in cell B must
be between 0.0 and 1.5, etc.

The Data Validation in the menu isn't the answer from what I can see -
can't validate various ranges for data. I am brand new to VBA -- is
VBA or a Macro the route to go? What type of code am I needing to
create here? I've looked through the help files and an ebook or two,
but I'm not finding the right type of code. Any nudge in the right
direction would be appreciated.

Thanks,

Joe

 

[Doug Glancy]

Joe,

Data validation does work for a range of numbers, e.g., between 0 and 1.5
inclusive. Just choose "Decimal" and set the min and max.

To have different validation ranges in your second category that are based
on the first entry, follow the directions at Debra Dalgliesh's site:

http://www.contextures.com/xlDataVal13.html

hth,

Doug

 

[jej1216]

Thanks, Dug. That site is great - gave me what I needed.

It does lead me to another question. Is there any way to force the
user to use the drop-down arrow to select the values rather than plow
over the validation and type in the value they want? If I stick to the
drop downs, everything works great. However, if I type in a value, the
out of range value is accepted.

TIA from an Excel neophyte.

Joe