#VALUE! Error

[juneau730]

I am at my wits end. I have a spreadsheet that was created by someone else, that has a lot of embedded formulas in it. We need these formulas to simplify a large audit we before, but when specific fields are empty, others display the #VALUE ! error.

I have tried different conditional formatting of the cells, such as the ;;; trick, I have tried the IFERROR (,0) trick, none of them work.

I really need to make the #VALUE ! error hidden or null, but still process the formulas when needed. I am trying to add a macro to the workbook, to automate our data moves throughout the different worksheets, but the #VALUE ! error is stopping the macros from processing.

Any help would be greatly appreciated.

 

[Norie]

What formulas are in the fields that are 'empty'?

 

[juneau730]

This is one of the cell formulas for a cell that displays the #VALUE ! error.

=DAYS(TODAY(),F2)

Here is the formula from cell F2 that, when populated (with a date in this case) would display a numerical value in cell J2 (where the #VALUE ! is)

=IF('VistA RAW DATA'!F2="","",DATEVALUE(LEFT('VistA RAW DATA'!F2,12)))

If I saw where to, I would upload the workbook

 

[fhqwgads]

maybe remove datevalue so it looks like this?

=IF('VistA RAW DATA'!F2="","",LEFT('VistA RAW DATA'!F2,12))

 

[fhqwgads]

wait nevermind that probably isnt it
i'm using excel 2007 which doesnt have the days function but just going of the support office site it says

If date arguments are strings that cannot be parsed as valid dates, DAYS returns the #VALUE ! error value.

so i thought using datevalue to get the date serial number like 43327 wouldnt work when used in the days function but i dont see why it shouldnt

 

[Norie]

I think the problem could be that your formulas could be returning "".

You might not want to change that but it should be able to deal with the #VALUE ! errors.

For example in you could have this in J2.

=IFERROR(DAYS(TODAY(),F2),"")

Another option might be to return 0 rather than "" and then use formatting to show empty cells rather than 0s.

 

[juneau730]

Thanks all, I will try the suggestions and see what happens. I had tried the IFERROR before and it didn't "hide" the #VALUE ! but that could mean I had the format incorrect.

 

[juneau730]

@Norie, I tried your IFERROR and I must have had the formatting incorrect when I originally started on this. It "hide" the #VALUE ! error on the cells I tried it on. Now to go through the whole doc and apply it, then test my macros.

Now to figure out a quick way to change the formulas, without having to go one cell at a time.

 

[juneau730]

Woohoo! Thank you so much Norie, I got it