Data Validation with IF Function and Multiple Min/Max Values

[Linda B]

Hi,
I have a spreadsheet with Data Validation in Column M showing List A through
H.
Example: Cell M3 can either be null or have value A, B, C etc thru H

I need to validate in Cell O3, based upon what they did in cell M3. Here's
an example:
If M3 is null, then O3 can either be zero (0) or can have value with min
= .01 and max = .04. User is not allowed to enter anything >0 and <.01, but
we want to allow them to enter 0.

If M3 is not null (user selected from list A thru H), then min = 0 and
max = 0.

Thanks for any help. I've tried a variety of statements with IF/OR and
nothing has worked so far.

Thanks for any assistance,
Linda

 

[Sheeloo]

I think the article at http://www.contextures.com/xlDataVal02.html will solve
your problem..

Let me know how it goes...

 

[Linda B]

Hi Sheeloo,
thanks, I read through the article, but don't think this quite captures what
I need (but I'm bookmarking the page!) Perhaps I didn't explain very well in
my first post.

I have a data validation list in Column M - the user may leave blank or may
select from items A thru H. That is working fine. Then, in Column O, I have
more data validation, which needs to be based upon Column M.

If the user left Column M blank, then we want the user to EITHER put a 0
(zero) in Column O -- or input a value between 0.01 and 0.04 (basically 1% -
4%). The user is not allowed to input a value >0 and <0.01.

If the user did not leave Column M blank (selected any item from the list to
populate Column M) -- then we want to prevent the user from inputting any
value other than 0 (zero) in Column O.

I have been using the Data Validation for Decimal with min/max - this allows
me to let the user input a value min .01 and max .04 IF column M is blank.
But, the problem is that I can't get it to ALSO allow the user to input just
a 0 (zero). And we want to allow the user to indicate 0 instead of just
leaving blank.

Thanks for any further assistance!
Linda

 

[Sheeloo]

I thought you could have a list of 0, 0.1, 0.2,... to choose from based on
the selection in the first field.

Looks like you all all values between 0.1 and 0.4 ...

I will explore and get back to you

 

[Debra Dalgleish]

Select the cells in column O where you want the data validation.
In this example, cell O1 is the active cell in the selection

Choose Data>Validation
For Allow, choose Custom
In the formula box, enter:
=IF(M1="",OR(O1=0,AND(O1>=0.01,O1<=0.04)),O1=0)
Remove the check mark from 'Ignore Blanks'
(optional) Add an Error Alert message
Click OK

 

[Linda B]

That worked. I was struggling with trying to use the Min/Max with Decimal.
Custom is where it's at.
THANKS!