Excel 2007 Data Validation Custom Formula If Cell is Blank/Null

Posted on 2011-02-10
Medium Priority
Last Modified: 2012-05-11
I am trying to create a data validation using the custom > formula means. If there is a better way, please advise:

Referencing Cell F4, for instance, I use the Data Validation > Allow: Custom > Formula:


Cell G3 holds a symbol (a checkmark).  [I could use ü and then convert the F column to Wingdings where it would read it as a check mark.  i.e., =IF(ISBLANK(F4),"","ü")].

When I click a space in F4, I receive the error message:  "The value you entered is not valid.  A user has restricted values that can be entered into this cell."

Is this a Circular Reference issue?  If so, how may I go about solving what I want to do?

I don't have to use a checkmark if that complicates things.  Just something where all a person needs to do is hit the spacebar in column F and 'something' will appear.

Question by:kristibigo
  • 2
LVL 15

Expert Comment

ID: 34864353
The data validation custom formula is a formula which should return true or false (true if the value in the cell is valid, and false if not).

I think you should put this formula directly (on the formula bar) on the cell beside the cell you enter the text (i.e. G4), so G4 will have "" if F4 is empty, or "ü" if F4 has some content.

Hope it helps.
LVL 45

Accepted Solution

patrickab earned 2000 total points
ID: 34864368

You have effectively, through DV, entered this formula into cell F4:


and that doesn't work because F4 is checking F4, Also F4 will never be blank as it has the formula in it.

However you could use:


and format to wingdings to give a check mark (tick)


Author Closing Comment

ID: 34865226
Ah ha! Yes, I agree that is a simple work-around! Thanks!

It works!  

Thank you for also verifying why the Data Validation doesn't work when it's checking for the very cell it is validating.
LVL 45

Expert Comment

ID: 34865269
kristibigo - Thanks for the grade - Patrick

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question