Solved

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

Posted on 2011-02-10
4
1,388 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 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now