Leave a cell COMPLETELY blank if there is an error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hello - 19 Nov 2007 16:27 GMT

My question is similar to many others in this discussion group, but I have
searched pretty thoroughly and not found the answer to my exact question...

What I want to do is to leave a cell completely blank if the result of an
ISERROR test (shown below) is true. I need that cell to be completely blank,
because in another cell I have an INDIRECT formula that relies on the COUNTA
function that refers to this first cell. If the IF(ISERROR...) formula
deposits a "" in a cell, it will be counted in the COUNTA formula, and
INDIRECT will pull the (nonexistent) value from this cell.

(This formula is on a sheet named calc)
=IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100))

=INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F)+23))

Is there a way to do this? Thank you!

Reply to this Message

Jon Peltier - 19 Nov 2007 16:34 GMT

We've been asking for some kind of BLANK() or NULL() worksheet function for
some time. Haven't gotten it, so no, there's no way to do this. Unless of
course you want to use VBA to clear the cells with errors, and then if the
data changes, you need to repopulate the cells with formulas.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> My question is similar to many others in this discussion group, but I have
> searched pretty thoroughly and not found the answer to my exact
[quoted text clipped - 15 lines]
>
> Is there a way to do this? Thank you!

Reply to this Message

Gary''s Student - 19 Nov 2007 16:41 GMT

If you don't want to count the pseudo-blank, why not use COUNT() rather than
COUNTA()?

Signature

Gary''s Student - gsnu200757

> My question is similar to many others in this discussion group, but I have
> searched pretty thoroughly and not found the answer to my exact question...
[quoted text clipped - 12 lines]
>
> Is there a way to do this? Thank you!

Reply to this Message

hello - 19 Nov 2007 20:21 GMT

Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I
also have text in the column, which does not counted, but it is easier to
work around that so I will switch. Would be nice to have something that would
leave a cell totally blank.

> If you don't want to count the pseudo-blank, why not use COUNT() rather than
> COUNTA()?
[quoted text clipped - 15 lines]
> >
> > Is there a way to do this? Thank you!

Reply to this Message

Gary''s Student - 19 Nov 2007 21:09 GMT

If you have Text in some cells, then a different approach is needed:

In place of:
COUNTA(calc!$F:$F)
use:
65536-COUNTBLANK(calc!$F:$F)

Signature

Gary''s Student - gsnu200757

> Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I
> also have text in the column, which does not counted, but it is easier to
[quoted text clipped - 20 lines]
> > >
> > > Is there a way to do this? Thank you!

Reply to this Message

Jon Peltier - 20 Nov 2007 02:34 GMT

I was going to say that "" isn't blank, and in fact, ISBLANK(A1) returns
FALSE if A1 contains "". However, COUNTBLANK(A1) returns 1 whether A1
contains "" or whether A1 is completely blank. Nice to know.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> If you have Text in some cells, then a different approach is needed:
>
[quoted text clipped - 37 lines]
>> > >
>> > > Is there a way to do this? Thank you!

Reply to this Message

Gary''s Student - 20 Nov 2007 13:48 GMT

The fact that ISBLANK() and COUNTBLANK() treat functions differently allow us
to count either way.

Signature

Gary''s Student - gsnu200757

> I was going to say that "" isn't blank, and in fact, ISBLANK(A1) returns
> FALSE if A1 contains "". However, COUNTBLANK(A1) returns 1 whether A1
[quoted text clipped - 48 lines]
> >> > >
> >> > > Is there a way to do this? Thank you!

Reply to this Message