Data Validation using a Custom Formula and a Cell Reference

[shello1981]

Hi Everyone,

I was wondering if anyone has encountered a problem like this and if there was an adequate workaround without VBA. I think this is an Excel bug or I am missing something.

I have to determine if an ordered quantity of product is valid by ensuring the customer orders in casepacks. Essentially, if my case pack is 2, the customer can only order in 2,4,6,8. Orders of 1,3, 7, 9 are invalid.

To do so I encapsulated the MOD function within an IF statement to divide the two numbers and give me an "Approved" or "Check Value" result.

If anyone needs it : =IF(MOD(R16,I16),"Check Value","Approved")

R16 is quantity ordered, I16 is casepack value. This works perfectly..and returns either an Approved or a Check quantity on the cell.


The problem is when I try to use the Data Validation function on the Ordered Qty cell, and reference my "Approved/Check" value cell for the formula. It seems to work perfectly for the first 5- 10 tries, then it just goes nuts...technically speaking .


Ideas?

[mcmahobt]

Can you post sample data?

Or specifically, what data validation method are you using?

[Richard Buttrey]

Hi,

Are you able to upload the workbook and explain exactly what you are entering when you encounter the apparent problem?

[shello1981]

Hi Richard, The sample Data is below. I am using the "Data Validation" from the Ribbon under Data- Data Validation, then using a setting of Allow:"Custom" , Formula : "=U16=Approved"

cell U16 contains the formula for my data validation. IF(MOD(R16,I16),"Check Value","Approved")

Case Pk Qty Ordered total Comments



2 1.00 15.44 Check Value
2 12.00 247.08 Approved
1 12.00 308.88 Approved


6 12.00 12.36 Approved
3 12.00 14.88 Approved
6 12.00 12.36 Approved
6 12.00 18.36 Approved
6 12.00 16.32 Approved

[shello1981]

After about 5- 10 attempts, no matter what I enter in the data validation sheet is treated as "Invalid" by Excel and flags the user with the Message. I am trying to get more detail on the issue. At this point, what seems to be occuring is that after 3-4 repeated tries, everything entered into the cell with data validation is "invalid"

[shello1981]

How do you upload a file here?

[Richard Buttrey]

Hi,

Check the 'Reading & Posting messages' in the FAQ.

[shello1981]

Thanks all for your attention. I have solved this by eliminating the step of a cell reference within the data validation and putting the formula directly into the Data Validation text box. What I suspect was happening is an issue of event timing and that data validation occurs right after the value in the cell is entered. Therefore, not giving it time to reference the "Valid/ Invalid" cell reference

1) A valid value is added in R16
2) Reference cell Switches to "Approved"
3) DataValidation allows the cell value to be filled.

1) Invalid value entered
2) DataValidation flags the value as invalid, but still retains the value in the cell.
3) Reference value switches to "Check Value"
4) Attempt to correct value in Data Validation cell.
5) Correct value is entered
6) Datavalidation flags this as invalid, because the reference is still holding a "Check Value" in Excel memory


Thats my best explanation for why I think, cell references wont work too well with Data validation formulas.