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|
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?
I don't understand what you mean by "not in continuous rows". However, would you check whether the following will work?
Adapt the range to your situation.
And, would you evaluate the following formula next to a cell that gives the trouble?
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.
> 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.
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