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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,334
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum.

Remove the checkmark in the box beside Ignore Blank in the validation criteria window.
 

Yago87

New Member
Joined
Jan 3, 2018
Messages
9
Thanks for the suggestion but that didn't have any affect on the issue.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,334
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

Yago87

New Member
Joined
Jan 3, 2018
Messages
9
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?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,334
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,334
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,818
Messages
5,598,280
Members
414,223
Latest member
Accountant2B

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
Top