Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return cell reference of lookup value | Excel Worksheet Functions | |||
returning blank cell in criteria o | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions | |||
Look for change next blank cell in Range | Excel Worksheet Functions |