?
Solved

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

Posted on 2011-02-10
4
Medium Priority
?
1,452 Views
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:

=IF(ISBLANK(F4),"",G3)

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.

Thanks.
0
Comment
Question by:kristibigo
  • 2
4 Comments
 
LVL 15

Expert Comment

by:gplana
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.
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 34864368
kristibigo,

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

=IF(F4="","",G4)

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:

=IF(G4="","","ü")

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

Patrick
0
 

Author Closing Comment

by:kristibigo
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34865269
kristibigo - Thanks for the grade - Patrick
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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