Leave a cell COMPLETELY blank if there is an error
 

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!

Leave a cell COMPLETELY blank if there is an error
 

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
_______


"hello" wrote in message
...
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!

Leave a cell COMPLETELY blank if there is an error
 

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


"hello" wrote:

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!

Leave a cell COMPLETELY blank if there is an error
 

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.

"Gary''s Student" wrote:

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


"hello" wrote:

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!

Leave a cell COMPLETELY blank if there is an error
 

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)
--
Gary''s Student - gsnu200757


"hello" wrote:

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.

"Gary''s Student" wrote:

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


"hello" wrote:

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!

Leave a cell COMPLETELY blank if there is an error
 

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
_______


"Gary''s Student" wrote in message
...
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)
--
Gary''s Student - gsnu200757


"hello" wrote:

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.

"Gary''s Student" wrote:

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


"hello" wrote:

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!

Leave a cell COMPLETELY blank if there is an error
 

The fact that ISBLANK() and COUNTBLANK() treat functions differently allow us
to count either way.
--
Gary''s Student - gsnu200757


"Jon Peltier" wrote:

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
_______


"Gary''s Student" wrote in message
...
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)
--
Gary''s Student - gsnu200757


"hello" wrote:

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.

"Gary''s Student" wrote:

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


"hello" wrote:

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!