Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

#VALUE! Error Message from Formula Referencing a Blank Cell

Posted by Ted Dankovich on December 13, 2001 12:17 PM
I'm using Excel 2000 to create a monthly financial consolidation worksheet. The source data is created from an Excel worksheet that imports data from a Unix software application.

If there is no source data in a cell from the extract, then the cell is "blank" (similar to using the spacebar to populate the cell). I use the ISERROR function to satisfy formula the first referenced cell that calls the extract data (with 0 as the first criteria if the original cell is blank) Using 0 does not create a number format or populate the field, it remains blank. Otherwise I get the value from the extract data source,which populates the data into my analysis correctly....

Now I want to Sum the data to create totals. When I sum the data fields, if a "blank" field exists I get the #VALUE! error message. I've tested the formats to find that I need to remove the format from the source data (written by a 3rd party developer).

I need to identify a formula function that will either ignore the "blank" format and sum the remaining cells or a macro to reformat the "blank" imported cells without disturbing the other data.

I am automating this to be a monthly process, so a manual re-format will not be an option.

Please e-mail any suggestions that will resolve this issue. Thanks!


Check out our Excel Resources

Re: #VALUE! Error Message from Formula Referencing a Blank Cell

Posted by Gary on December 13, 2001 12:25 PM
You could use an array formula along the lines of =sum(if(Sum_range="",0,Sum_range)).

Gary


Re: #VALUE! Error Message - Array Formula

Posted by Ted on December 13, 2001 12:34 PM
Great Idea Gary, but didn't work. The data I want to sum is not in continuous rows. I tried to sum the individual cells in your formula, still got the #VALUE! result. Added IF(ISERROR... function and only got a 0 as a result. Need sum of data.
Thanks...


Re: #VALUE! Error Message from Formula Referencing a Blank Cell

Posted by Aladin Akyurek on December 13, 2001 12:45 PM
Ted --

Normally, a cell containing an ordinary space or an empty cell shouldn't cause a #VALUE! error, becasue SUM will ignore such a cell.

Care to post the formula that you use?

Aladin


Re: #VALUE! Error Message - Array Formula

Posted by Aladin Akyurek on December 13, 2001 1:13 PM

Tedd --

I don't understand what you mean by "not in continuous rows". However, would you check whether the following will work?

=SUMPRODUCT((ISNUMBER(A1:A100)+0),(A1:A100))

Adapt the range to your situation.

And, would you evaluate the following formula next to a cell that gives the trouble?

=CODE(the-cell-that-gives-trouble)

Aladin

===========


Re: #VALUE! Error Message - Formula Used

Posted by Ted on December 13, 2001 1:20 PM
Here's the formula:

IF(ISERROR(+e11+e16+e29)="",0,+e11+e16+e29)where e29 formula is IF(ISERROR(+WK1!d45),0,+WK1!d45).

In this example, e29 returns a blank cell resulting from WK1!d45 being a blank cell.

If I clear the format in WK1!d45 or place a number in WK1!d45, then the formulas return the correct answer.

Ted--


Re: #VALUE! Error Message - Formula Used

Posted by Aladin Akyurek on December 13, 2001 1:44 PM

> IF(ISERROR(+e11+e16+e29)="",0,+e11+e16+e29)where e29 formula is IF(ISERROR(+WK1!d45),0,+WK1!d45).

I think the following formula should simply work.

=SUM(E11,E16,E19)

where e19 is simply =WK1!d45 if this WK1 cell is numeric or otherwise contains a space as you stated earlier.

You can check what is in WK1!d45 when it's non-numeric by using

=CODE(WK1!D45)

Aladin

===========


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.