custom data validation formula

Yago87

New Member
Joined
Jan 3, 2018
Messages
9
I'm using Excel custom data validation to restrict entry in D2, if D1 is 0 or blank. The formula =D1>0 works just fine if D1 is a number greater than 0, but if DI is blank, the restriction does not work. Help for this formula please?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Welcome to the forum.

Remove the checkmark in the box beside Ignore Blank in the validation criteria window.
 
Upvote 0
It does indeed have an effect (not affect). The problem is that when a DataValidation rule is created and applied, the applicable cell can still contain what you think is a disallowed value. Only when you try to change it later will the rule be invoked and affect the user's ability to enter a value.

I put -3 in D1. I created a Custom DV rule in D2 with =D1>0 and the Ignore Blank box un-checked. Then I tried to type something into D2 and the entry was blocked by the DV rule. I deleted the contents of D1, which made D1 blank; t
hen I tried to type something into D2 and the entry was blocked by the DV rule. These are the expected behaviours: the DV rules only have effect when you try to enter values into the applicable cell - they do not affect existing entries.
 
Last edited:
Upvote 0
Thank you for taking the time to reply. I originally used the exact same logic you stated but was still having trouble. However, I now discovered that the problem is related to how a number is deleted in D1. If I delete the number in that cell by clicking "clear contents" for example, the rule works just fine. If I remove the number by simply pressing the space bar, then the rule does not work. Since this sheet will be used by employees of mine (who I suspect will use the space bar method), I need to find a very user-friendly way to allow them to use this sheet. Perhaps I need to format cell D1 in a different way, so that the DV rule has the proper effect regardless of how numbers are deleted?
 
Upvote 0
Pressing the space bar means the cell is not blank - it has a space, which is a character. Instruct your workman to press the delete key instead.
 
Upvote 0
It might help you and your team to watch some instructional videos about Excel. The ExcelIsFun channel on youtube is a depository of thousands of insightful ones. Mike just started a new series regarding Business Math that might help; here is the first in the series https://youtu.be/8TB3VNS2sTU
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top