• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom Data validation [SOLVED]

C

Cammandk

Member
I have an invoice amount field to be entered. I don't want the user to be able to input a value unless they have made a selection in COL D (Type) and COL E (Date).

If they have made the selections then the amount field needs to be data validate based on decimal Min in Setup!$A$21 and Max in Setup!$A$22.


This functions needs to happen over multiple rows on the main sheet.


Thanks
 
Hi David ,


Can you clarify / confirm a few points ?


1. Are the cells in columns D and E blank to start with ?


2. Are they then populated using drop-downs ? If so , can I assume that the drop-downs are of List type , or are there formulae to decide the allowed types and dates ?


3. The DV is to be set up on a sheet which is different from the sheet labelled Setup , where the MIN and MAX limits for the amount field are.


If all of the above are correct , then you can have a DV formula such as :


=AND(NOT(ISBLANK($D2)),NOT(ISBLANK($E2)),$M2>=MINVAL,$M2<=MAXVAL)


where I have assumed that your amounts data entry is in column M , and starting from M2.


MINVAL and MAXVAL are named ranges , referring to Setup!$A$21 and Setup!$A$22.


Narayan
 
Yes your assumptions are correct. - COL D is a dropdown. However COL E is a date field

Have named my DV as you have.


=AND(NOT(ISBLANK($D7)),NOT(ISBLANK($E7)),$N7>=MinVal,$N7<=MaxVal)


Not allowing me to enter anything even when D & E fields populated - is it because E is a date field?
 
Hi David ,


It works on my system !


Can you try one thing ? Remove the DV from cell N7 , and enter the DV formula :


=AND(NOT(ISBLANK($D7)),NOT(ISBLANK($E7)),$N7>=MinVal,$N7<=MaxVal)


in any unused cell , say M1.


Now check out the result of this formula for various conditions :


1. With either D7 or E7 or both blank


2. With N7 having data within the limits


3. With N7 having data outside the limits


If the formula always shows the correct result , then it should work in the DV also.


Narayan
 
I have tried in a seperate cell and it is giving the correct True/false response.

I then put it into custom data validation option for N7 and it doesn't allow.

David
 
Hi David ,


I think the only solution is for you to upload your file ! That way we can work with the problem , because on my computer I cannot reproduce your problem ; it works the way it should.


I hope your defintions of the named ranges MinVal and MaxVal are proper , and the values in the cells Setup!$A$21 and Setup!$A$22 are numeric values ?


Narayan
 
Hi Narayan


Can I just confirm that I copy the formula into the custom forumula field on data validation.

I have done this on a new worksheet and although the results true/false all appear correct when just a formula in a cell as soon as I add into data validation box it doesn't work. Not able to upload file at this stage.

Thanks

david
 
Hi David ,


Yes , that is what is to be done ; click on Data , Data Validation , Data Validation , Custom and enter the formula in the formula input box.


Narayan
 
Hi Narayan


back on this issue - i've upload a sample wb with the fields in.

when i try to enter value in the amount field it won't let me even though the other fields are completed


Thanks

david


https://www.box.com/s/vbahzxygerk8lnipojca
 
Hi David ,


First clear all the validation rules in the range B8:D100 ( I have just extended the data entry range till row 100 ).


Select the range D8:D100 , and enter the following DV rule :


=AND(NOT(ISBLANK($B8)),NOT(ISBLANK($C8)),$D8>=MinVal,$D8<=MaxVal)


Narayan
 
Sorry this is still not working - its letting me input the value even though only on field filled.


can you up load the working sheet. the code that you have above is what i had in the worksheet and it didn't work?


do you have an email address I can then allow you to upload to my box folder?


Thanks

David
 
You must log in or register to reply here.
Back
Top