Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cwinters
 
Posts: n/a
Default use IF to return a truly blank cell


I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY
empty. If I fill that formula down, I cannot, for example, jump from
one "ERROR" cell to the next by hitting Ctrl arrow-down because it
seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a
TRULY empty cell?

Thanks


--
cwinters
------------------------------------------------------------------------
cwinters's Profile: http://www.excelforum.com/member.php...o&userid=18386
View this thread: http://www.excelforum.com/showthread...hreadid=393270

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

Nope.

If you don't need the formula, you could use this:

=if(a1=0,"Error","deletethis")
Drag down the column
convert to values (edit|copy, edit|paste special|Values)

Then edit|replace
what: deletethis
with: (leave blank)
replace all.



cwinters wrote:

I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY
empty. If I fill that formula down, I cannot, for example, jump from
one "ERROR" cell to the next by hitting Ctrl arrow-down because it
seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a
TRULY empty cell?

Thanks

--
cwinters
------------------------------------------------------------------------
cwinters's Profile: http://www.excelforum.com/member.php...o&userid=18386
View this thread: http://www.excelforum.com/showthread...hreadid=393270


--

Dave Peterson
  #3   Report Post  
swatsp0p
 
Posts: n/a
Default


If you think logically, the cell is NOT empty, (truely or otherwise) as
it contains a formula. What you are really doing is forcing the cell
to not display its contents with IF(.....,"").

AFAIK, there is no way to make a cell with a formula act as if it
doesn't exist.

One option to explore is to use Find. Highlight the column you want to
search. Click CTRL+F. In the "Find What", enter the Error (#N/A, #VALUE,
etc.). Then click on Options to search "By Columns" and set Look In to:
Values.

Not too bad, especially if you have a very long column to look
through.

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393270

  #4   Report Post  
cwinters
 
Posts: n/a
Default


Yes but... I forgot to mention a key step I do. After running this IF
formula I always copy and paste special the cells as values. So the
formulas no longer exist. The "" is turned into nothingness that is
not, truly, nothingness. The Find technique is great if you want to
find and replace something within a given cell, but I want to find and
merely TAG something (in an adjacent cell; hence using the formula)


--
cwinters
------------------------------------------------------------------------
cwinters's Profile: http://www.excelforum.com/member.php...o&userid=18386
View this thread: http://www.excelforum.com/showthread...hreadid=393270

  #5   Report Post  
Gary's Student
 
Posts: n/a
Default

Use an event macro.

If cell A1 changes and is zero then set the result cell to "ERROR"
if cell A1 changes and is not zero then set the result cell to ""
--
Gary's Student


"cwinters" wrote:


I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY
empty. If I fill that formula down, I cannot, for example, jump from
one "ERROR" cell to the next by hitting Ctrl arrow-down because it
seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a
TRULY empty cell?

Thanks


--
cwinters
------------------------------------------------------------------------
cwinters's Profile: http://www.excelforum.com/member.php...o&userid=18386
View this thread: http://www.excelforum.com/showthread...hreadid=393270




  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

After you convert to values, you can see that those "empty" cells aren't really
empty.

Tools|Options|Transition tab|check Transition navigation keys
(remember to toggle it off later!)

Now look at the formula bar for one of those empty cells--you'll see a single
quote.

I usually use a formula like:

=if(a1=0,"ok",na())

convert to values and then do that Edit|replace on the #n/a! string.

(Kind of the same response as the deletethis message, huh?)

cwinters wrote:

Yes but... I forgot to mention a key step I do. After running this IF
formula I always copy and paste special the cells as values. So the
formulas no longer exist. The "" is turned into nothingness that is
not, truly, nothingness. The Find technique is great if you want to
find and replace something within a given cell, but I want to find and
merely TAG something (in an adjacent cell; hence using the formula)

--
cwinters
------------------------------------------------------------------------
cwinters's Profile: http://www.excelforum.com/member.php...o&userid=18386
View this thread: http://www.excelforum.com/showthread...hreadid=393270


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return cell reference of lookup value bobm Excel Worksheet Functions 3 July 7th 05 08:49 AM
returning blank cell in criteria o Joop Excel Discussion (Misc queries) 3 June 3rd 05 02:11 PM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM
if the value of a cell in a range is not blank, then return the v. kvail Excel Worksheet Functions 2 April 8th 05 10:07 PM
Look for change next blank cell in Range Nigel Bennett Excel Worksheet Functions 1 March 13th 05 10:45 PM


All times are GMT +1. The time now is 06:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"