#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!