Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-10
4
Medium Priority
?
1,435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

618 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