Solved

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

685 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