Help With Conditional Formula

[Dsheaff]

I'm missing something!!... Hoping someone can help.

=IF(AND('QC W Beam'!O8=1,'QC W Beam'!I7="GUARDRAIL REPAIR (W & THRIE BEAM)"),'QC W Beam'!IM165,"")

This formula delivers the number 0 (zero) to the recipient cell (cell N56 on the worksheet titled QC Summary Rail.. The first and second elements deliver "TRUE" ... The third element 'QC W Beam'!M165 delivers 0 (zero). The referenced cell, M165, on the worksheet QC W Beam is filled with the value FAILED

All referenced and related cells are formatted as "General"

 

[CA_Punit]

What you get if you simply type "NN" in N56??


is it showing 0

 

[jasonb75]

The referenced cell, M165, on the worksheet QC W Beam is filled with the value FAILED

Does the cell actually contain the word "FAILED" or is it shown by conditional formatting when M165 contains 0?

If the latter is the case (which seems the most likely possibility) then any formulas referring to that cell will still see the 0 value in the cell, not the label that is applied to it by conditional formatting.
To work around that you would need to either apply the same conditional formatting to the cell with the new formula, or add another condition into your formula. The best way would depend what you're doing with the data afterwards.

 

[Dsheaff]

The cell containing the value failed cell M165), is formatted as follows: =IF(AND($P$169>=1,G13>=1),"FAILED","") Prior to introducing the conditional statement you see in my original transmittal above, the cell was simply formatted as ='QC W Beam'!M165 . At that time, cell M165 was formatted as I've mentioned here.... and the correct value FAILED was delivered.

 

[jasonb75]

The only thing that comes to mind is that you have a circular reference somewhere that is causing the problem. I can see no other reason for it to happen.

The formula that you have shown us can only return one of 2 things under normal conditions. Either the content of M165, or a blank.

If M165 contains anything other than 0 then it means that there is something not normal about your file. Without having the actual file to diagnose it is going to be impossible to find the cause.

 

[Dsheaff]

So how do I get those pieces of the file to you?

 

[jasonb75]

You would need to upload it to a file sharing site then post the link in the forum.

 

[Dsheaff]

Per your instruction, I have posted pieces of my file relative to the issue I've raised above to a file sharing site and posted a link to same below. Would appreciate you taking one more look at this issue. What ever help or guidance you can provide is greatly appreciated.

Ge.tt | Gett sharing

Ge.tt lets you share images, documents, videos and files with one click. Your files are available instantly with our unique real-time technology. The smallest step in file sharing.

ge.tt

 

[jasonb75]

Even looking at the file, it took me a while to find it, you have an almost impossible to see typo.

Your formula is looking at IM165 not M185.

 

[Dsheaff]

Thank you ever so much Jason. You made my day.... Now for a return trip to the optometrist.