asked on

Excel 2007 Validation Issues

I need some assistance. I have been trying to validate some boxes on a spreadsheet that we want our users to use as an order form. I have tried the data validation, but it let's you continue on for the Facility Code. Here is what I need per the attached screen:

1) On B5, the Facility Code must be equal to 7 or 10 digits. Nothing else.
2) On D9, the Vendor Order# must be equal to 10 digits.
3) On H14, this is weird how they have it. The number has to be divisible by E14. For example, if it says 24, they can ONLY enter 24, 48, 72, etc.

Thanks for the assistance!

nutsch

1) Use Data validation custom formula is =OR(len(B5)=7, len(b5)=10)
2) =len(b5)=10
3) =MOD(H14,E14)=0

sharpapproach

ASKER

Dear nutsch,

I had don the #1, but found that it allows you to continue on, even though you get the message. You can also paste in the cell to get around it.

How can you force them to never leave the cell until the condition is met?

Thanks.

nutsch

Meaning you want them to be stuck to the cell for ever, without possibility of leaving it blank? You can't do that with Data Validation, but you can combine more validations. E.G. you can put in the quantity data validation that the facility code needs to be input, as in

=AND(MOD(H14,E14)=0,OR(len(B5)=7, len(b5)=10))

If that's still not enough, you might have to use macros.

ukerandi

check this
Book5.xlsm

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

Here's one enhancement that takes it a step further, using a couple additional variables and the IsValid() function, we test to see if the user went to a cell with data validation - it makes sure that that user is returned to that cell if proper data is not entered, the cell is cleared and the user clicks elsewhere, even on another tab in the workbook. Not sure you want to go this far, but the attached demo is there for you to test.

There are also a couple additional prompts to advise the user that proper data must be entered, an UNDO is performed assuming there was good data in the cell before it was inadvertently cleared, etc.

I also fixed a minor bug in the buildValidationRange() subroutine, as you would get an error by clicking on a sheet tab that didn't have any data validation cells.

Let me know if this additional step is useful.

Dave
validationTest-r2.xlsm

sharpapproach

ASKER

Awesome Thanks! sorry, I used it and forgot to close it until I just came to check something out.