Hi Mynda
I've created a custom data validation that uses the formula =$E$12<=$D$2 where E12 is the total of the amount the supplier has spent and D2 is the amount that the supplier received. This works great but they can still enter in text e.g. writing in two hundred instead of 200 which I want to prevent.
I've tried using data validation based on decimal or whole numbers using all sorts of variations of between with min and max values but I no longer get the desired result.
When I enter min = $E$12<=$D$2
and max = $E$12<=$D$2
I can't enter any number value.
When I set the min value to 0 and keep max = $E$12<=$D$2, I can enter any value beyond the amount in D2 (the amount the supplier received). I've attached a file for your reference.
I'm completely stuck!
Thanks
Tim
Hi Tim,
Try choosing 'Custom' in the Allow drop down and then enter your formula as you had it in the 'Formula' field.
Mynda
Hi Mynda
Thanks for your response. I've already used the custom function with the formula =$E$12<=$D$2. This works well at restricting the total amount that can be entered into the cells but the user could still enter the number as text e.g. "two hundred" which I'm trying to prevent. This is why I've tried the whole number and decimal number in the 'allow' drop down box as my previous message but this doesn't seem to do the job. Any way round this you can think of?
Thanks again
Tim
P.S. Here's the example of the data validation that I've tried using whole number but I can't enter any values with this methods. I've attached a screenshot.
Hi Tim,
I'm not aware of any way to prevent this. However, you could set up some conditional formatting that checks the value entered into those cells is a number i.e. not text, and then present them with some text in red in the adjacent column that tells them that's not allowed.
Mynda
Hi Mynda
Thanks for your response. I've followed your suggestion and set up conditional formatting based on 'Format Only Cells That Contain'-->cell value between a to z which highlights any combination of text in the alphabet. Think this should do the job.
Thank you
Tim
