| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with IF Function and Multiple Min/Max Values
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation with IF Function and Multiple Min/Max Values
I think the article at http://www.contextures.com/xlDataVal02.html will solve
your problem.. Let me know how it goes... "Linda B" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation with IF Function and Multiple Min/Max Values
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" wrote: I think the article at http://www.contextures.com/xlDataVal02.html will solve your problem.. Let me know how it goes... "Linda B" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation with IF Function and Multiple Min/Max Values
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation with IF Function and Multiple Min/Max Values
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 DataValidation 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 wrote: 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" wrote: I think the article at http://www.contextures.com/xlDataVal02.html will solve your problem.. Let me know how it goes... "Linda B" wrote: 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 -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation with IF Function and Multiple Min/Max Values
That worked. I was struggling with trying to use the Min/Max with Decimal.
Custom is where it's at. THANKS! "Debra Dalgleish" wrote: 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 DataValidation 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 wrote: 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" wrote: I think the article at http://www.contextures.com/xlDataVal02.html will solve your problem.. Let me know how it goes... "Linda B" wrote: 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 -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Individual Validation for multiple values in single cell | Excel Worksheet Functions | |||
| Help for multiple & variable validation function | Excel Worksheet Functions | |||
| Data Validation with Changing Values | Excel Discussion (Misc queries) | |||
| Data Validation - combination of values | Excel Discussion (Misc queries) | |||
| Data Validation - but not restricting values | Excel Discussion (Misc queries) | |||
Linear Mode
