If all the cells within the given range are blank, the following
formula evaluates to "#VALUE!"

=IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32: 32)))

I'd be grateful if someone can tell me either how to correctly rewrite
the formula or, failing that, how to supress the error. The error
doesn't affect overalll performance. It's just rather unsightly.

Any help greatly appreciated.

The argument for ISBLANK can only be one cell, not a range

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"strawberry" wrote in message oups.com...
| If all the cells within the given range are blank, the following
| formula evaluates to "#VALUE!"
|
| =IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32: 32)))
|
| I'd be grateful if someone can tell me either how to correctly rewrite
| the formula or, failing that, how to supress the error. The error
| doesn't affect overalll performance. It's just rather unsightly.
|
| Any help greatly appreciated.
|

ISBLANK can be used over a range if it is used in an array formula.

=AND(ISBLANK(H42:Cs42)) resolves to TRUE if the range is blank and the
formula is entered with CTRL+SHIFT+ENTER.

However, even when entering your formula as an array formula I get a #VALUE!
error.

What are you trying to do with this formula?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Niek Otten" wrote:

The argument for ISBLANK can only be one cell, not a range

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"strawberry" wrote in message oups.com...
| If all the cells within the given range are blank, the following
| formula evaluates to "#VALUE!"
|
| =IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32: 32)))
|
| I'd be grateful if someone can tell me either how to correctly rewrite
| the formula or, failing that, how to supress the error. The error
| doesn't affect overalll performance. It's just rather unsightly.
|
| Any help greatly appreciated.
|

This formula works, however, entered as an array formula:
=IF(ISBLANK(H42:CS42),"",(MAX(Sheet1!32:32)))

I'm not sure what you're testing for with AND, CHAR, and MAXA....

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dave F" wrote:

ISBLANK can be used over a range if it is used in an array formula.

=AND(ISBLANK(H42:Cs42)) resolves to TRUE if the range is blank and the
formula is entered with CTRL+SHIFT+ENTER.

However, even when entering your formula as an array formula I get a #VALUE!
error.

What are you trying to do with this formula?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Niek Otten" wrote:

The argument for ISBLANK can only be one cell, not a range

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"strawberry" wrote in message oups.com...
| If all the cells within the given range are blank, the following
| formula evaluates to "#VALUE!"
|
| =IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32: 32)))
|
| I'd be grateful if someone can tell me either how to correctly rewrite
| the formula or, failing that, how to supress the error. The error
| doesn't affect overalll performance. It's just rather unsightly.
|
| Any help greatly appreciated.
|

On 22 Mar, 12:27, Dave F wrote:
ISBLANK can be used over a range if it is used in an array formula.

=AND(ISBLANK(H42:Cs42)) resolves to TRUE if the range is blank and the
formula is entered with CTRL+SHIFT+ENTER.

However, even when entering your formula as an array formula I get a #VALUE!
error.

What are you trying to do with this formula?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

"Niek Otten" wrote:
The argument for ISBLANK can only be one cell, not a range

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"strawberry" wrote in ooglegroups.com...
| If all the cells within the given range are blank, the following
| formula evaluates to "#VALUE!"
|
| =IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32: 32)))
|
| I'd be grateful if someone can tell me either how to correctly rewrite
| the formula or, failing that, how to supress the error. The error
| doesn't affect overalll performance. It's just rather unsightly.
|
| Any help greatly appreciated.
|

Switching to this seems to work:

=IF(COUNTA(H42:P42)=0,"",CHAR(MAXA(Sheet1!32:32)))

This is part of an Issue Sheet. It needs to keep track of the latest
revision letter (typically A-Z).
This formula simply looks at a range of cells to its right. If no data
is present, it evaluates to "". Otherwise it displays the highest
value present.
The corresponding worksheet (Sheet1) simply holds the letters as
(upper case) numeric values for comparison. Hope that makes sense.

Ta.

Makes sense now, and if you want to avoid array formulas, COUTNA over the
range is the way to go.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"strawberry" wrote:

On 22 Mar, 12:27, Dave F wrote:
ISBLANK can be used over a range if it is used in an array formula.

=AND(ISBLANK(H42:Cs42)) resolves to TRUE if the range is blank and the
formula is entered with CTRL+SHIFT+ENTER.

However, even when entering your formula as an array formula I get a #VALUE!
error.

What are you trying to do with this formula?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

"Niek Otten" wrote:
The argument for ISBLANK can only be one cell, not a range

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"strawberry" wrote in ooglegroups.com...
| If all the cells within the given range are blank, the following
| formula evaluates to "#VALUE!"
|
| =IF(AND(ISBLANK(H42:CS42)),"",CHAR(MAXA(Sheet1!32: 32)))
|
| I'd be grateful if someone can tell me either how to correctly rewrite
| the formula or, failing that, how to supress the error. The error
| doesn't affect overalll performance. It's just rather unsightly.
|
| Any help greatly appreciated.
|

Switching to this seems to work:

=IF(COUNTA(H42:P42)=0,"",CHAR(MAXA(Sheet1!32:32)))

This is part of an Issue Sheet. It needs to keep track of the latest
revision letter (typically A-Z).
This formula simply looks at a range of cells to its right. If no data
is present, it evaluates to "". Otherwise it displays the highest
value present.
The corresponding worksheet (Sheet1) simply holds the letters as
(upper case) numeric values for comparison. Hope that makes sense.

Ta.