Unwanted results from formula

[Gerd]

In my spreadsheet I have entered the following formula
into a cell: =D7*SHEET3!B7

The formula works fine except when there is no data in
either cell D7 or Sheet3!B7. I get #VALUE!

Is there a way that I can change the formula so that the
cell will be empty instead of saying #VALUE!

Thank you.

 

[Frank Kabel]

Hi Gerd
are you sure that there is no data in the other cells. I only get the
#VALUE error if in one of both cells a text is entered (e.g. a space or
the formula result ""). you can test this with
=AND(ISBANK(D7),ISBLANK('Sheet3'!B7))
this should return TRUE

But to circumvent your error, try the following
=IF(ISERROR(D7*'Sheet3'!B7),"",D7*'Sheet3'!B7)
or
=IF(AND(ISNUMBER(D7),ISNUMBER('Sheet3'!B7)),D7*'Sheet3'!B7,""

HTH
Frank

 

[Bob Phillips]

=IF(AND(ISNUMBER(D7),ISNUMBER('Sheet3'!B7)),D7*'Sheet3'!B7,""

Don't forget the closing bracket!

 

[Frank Kabel]

Hi Bob
thanks but whre hat my bracket gone (I copied it)???
Frank

 

[Bob Phillips]

Frank,

I know that feeling! You just can't trust these computers.

Bob