Team,



Please see attached. The upper section is my testing area for the formulas and the lower is the desired outcome. For the most part, the formula works with dates listed in the corresponding cells. However, there will be many cases of which instead of a date in column I (Calib Date), there will be entries of "NA" to indicate a calibration is not required. This is where my formula and additional attempts to tweak it fails such that I cannot obtain the desired status word in column E (Current Status) which contains the formulas.

Your help would be greatly appreciated.

Attached Files

Current Status Worksheet.xls (31.5 KB, 10 views)

At the first look, i noticed that your formula starts with:
=IF(F3="Red Tagged";"Red Tagged";IF(O.......
and in cell F3 you have the value Red Tagged.
in IF evaluations, the formula STOPS at the first TRUE evaluation, so the rest of your formula is not taken into consideration. Take care of the evaluation order, place them in the order you need.
You can use And or OR to this step: IF(AND(F3="Red Tagged";I3="NA");"Red Tagged";IF(O.......
Other error: in the formula , you use different evaluation for the same cell I3: "N/A" and "NA"... maybe it is easier to use only 1 rule for entering data...
if you let users enter data as they wish, you will get : N;A for a change, and your formula will not work..
OR(I3="N/A";$D$1<IF(I3="NA";0;I3)
IF(I3="NA";0;I3)

Catalin.B,

I thank you kindly for your reply. I did miss that one entry of N/A verus NA, unfortunately this does not fix my problem. I will explain in just a moment.

The reason that the qualifier for "Red Tagged" comes first is that it takes priority over all other conditions. If "Red Tagged" is true, I do not want the formula to continue testing the other conditions.

My problem is with column I with the heading of Calib Date. If the data within these cells are only dates, the formula works okay. However, in the "real world" there are valid entries which the line item does not require a Calib (short for calibration). Thus, what I am attempting to do with the formula is to treat any row entry that has "NA" in row I as a special case such that either I ignor the part of the formula testing the date comparison for that row or to force it to agree that the calibration is good within the limited perameters of the formula.

Did I make myself clear?

i tested your worksheet.. (played with NA and colours )
Which is the desired status word in column E for an "NA" entry in column I ?

here is your workbook with a little modiffication

Attached Files

Current Status Worksheet.xls (33.5 KB, 7 views)

I have had a quick look at the worksheet with the little modification, however, it still does not match the desired results as displayed in the lower half of the worksheet "Desired Results" (rows 19 through 31).

This should also answer your first question I believe.

Am i missing something?
If i copy the data from I19:I31 to range I3:I15, then your range E3:E15 has the same values as the desired range E19:E31.(?!)
The only difference is that in E3:E15, the conditional formatting formulas for the cell colors are not completed, it has only rules 1, 2, 6 and 8 applied from the range B36:B48, the rest of rules you have to write them yourself. Open the conditional formatting, manage rules, and add the other rules...

here is again you workbook

Attached Files

Current Status Worksheet.xls (33.5 KB, 10 views)

It surely must be me that is not communicating effectively. You are correct that when the cells I3:I14 are populated with dates, the adjusted formula works correctly as per the desired results. However, the formula is faulty when testing with the entry of NA in column I. For example, input NA into cell I15 and the returned result in E15 changes from Ready to Calib. A simular fault occurs when NA is the data for cell I9; the returned result in E9 changes from the desired result of String Check to Calib. This is the problem that I am trying to correct.

ok, it's better now
i replaced IF(I15="NA";0;I15) with IF(I15="NA";D$1;I15)on all positions and works for all except E15, but i think this is the key condition to make it work.
you have to find a replacement for the red values to make it work for all range of conditions