Cannot remove #value & format coloring from several rows in spreadsheet?

**willeebee** · 06-26-2013, 04:58 AM

Hello,

New here and very frustrated. I am redesigning a working production spreadsheet for package deliveries. Currently the working version only highlights an entire row in yellow when a package is signed out of the receiving room ("When Signout Date is entered"). I am adding another function where any package remaining in the receiving room for more than 6 months, turns the entire row pink in addition to the highlighted yellow as above. For whatever reason I cannot figure (I have zero experience with formulas and formatting) all rows beyond any already populated show either a #value error, or a crazy high number in the "Days In Storage" column and highlight the whole row pink. In addition, the pink row (If I try and enter data) doesn't work like the rest of the spreadsheet does. I need this in production yesterday and I'm just completely stuck. Any assistance would be fantastic. See attached copy.

Bill

**TMS** · 06-26-2013, 05:13 AM

Cell C293 has the formula: =TODAY()-B293

Cell B293 is blank, hence you get the number of days that reflects today's date.

Cell C294 has an error value generated by a formula which has been converted to text

Cell C295 has the formula: =TODAY()-B295 ... this generate s #VALUE error because cell B295 has a space in it. The pattern then follows down the sheet: space and error value.

You should change cell C293 to be: =IF(TRIM(B293)="","", TODAY()-B293)

And then amend your Conditional Formatting formula to incorporate a test that column C is not blank.

For example: =AND(C8<>"",C8>179,ISBLANK(M8))

Regards, TMS

**TMS** · 06-26-2013, 05:14 AM

Oh, and maybe you don't want/need the spaces in column B.

Regards, TMS

**Spencer101** · 06-26-2013, 05:19 AM

Have a look at the attached. Does this help?

**wenqq3** · 06-26-2013, 05:25 AM

#VALUE =This error is most often the result of specifying a mathematical operation with one or more cells that contain text.
there is a SPACE at your column B. Today()-SPACE = #value!.

Either you only drag the formula to the no empty column B cell or
at your cell E293 =

=IF(ISERROR(DATEVALUE(TEXT(B293,"mm/dd/yyyy"))),"",TODAY()-B293)

**TMS** · 06-26-2013, 05:45 AM

The TRIM(B293)="" caters for space and blank in column B. If the space is there for a reason, then this formula will cope with it.

And, in excel 2010, you can use =IFERROR rather than =IF(ISERROR( but the error has been eliminated by testing for blanks anyway.

Regards, TMS

**wenqq3** · 06-26-2013, 05:53 AM

if using IFERROR function, how to insert the TODAY() - date?

**TMS** · 06-26-2013, 06:10 AM

Well, you wouldn't really need to, but:

=IFERROR(TODAY()-B293,"")

The first parameter is the formula being monitored. The second parameter is the value to be assigned in the event of an error in the formula.

Regards, TMS