Errors with codes/formulas - Beginner mistake?

Hello All,

I consider myself lightly experienced in coding. I haven't been using "complex" Excel formulas for long so the below is probably a rookie error.
I am having an issue with the below formula:

This formula works:
=IF(ISBLANK(D197),"N/A",IF(D197="open",TODAY()-G197,"CLOSED"))

but when I want to add the function to make the cell in question display "ON HOLD" if cell D197 displays "ON HOLD", I only get the result: "CLOSED", even if cell D197 reads "ON HOLD".

=IF(ISBLANK(D198),"N/A",IF(D198="open",TODAY()-G198,IF(D198="ON HOLD","ON HOLD","CLOSED")))

Where is my mistake?
Is there an easier way of doing this all together?

Cheers guys,

JM

Possible data problem e.g. extra space in "ON hold" i.e.. " ON hold". Use TRIM function to remove unwanted blanks

I have triple checked the data, no anomalies found. I also tried the TRIM function but with no success.
Thanks for the suggestion though!
Any other ideas?

Re-post please

try for INDEX problem

=IFERROR(INDEX($B$2:$B$9, SMALL(IF(VALUE($A$13)=VALUE($A$2:$A$9), ROW($A$2:$A$9)-ROW($A$2)+1), ROW(1:1))),"")

Hello Ford,
That is indeed a typo. All cells referenced should read D197.
I have attached a version of the document with pretty much all of the fat trimmed away. The blue cells are the cells that change from line to line, the yellow cells are the cells containing the formulas in question. Row 7 shows the expected result.
I hope this is enough info!

Regards,
James

See attached: testing on column C (?)

I just copied your formula and changed D197 to C7

Hi John,

Thanks very much for your help & Advice here.
Your formula does indeed work, it seems when making the "formula-practice" document, I overlooked some incorrect cell references (This is why I titled this thread 'beginner mistake?' ").
When I copy your working formula into the main document, I still get the same error. I'm now thinking that this is an error with Excel 2013 or my PC.

So, just to clarify, the formula works when taken out of the document, but won't work (Returns "Closed", Instead of "on Hold") inside the document.

At least I can rest easy knowing my formulas are ok (with maybe some cell reference errors). Luckily, this formula is not overly important and only helps with a layout issue. This is something that can be worked around.

Thanks again all,

James

Is it possible to send an extract of the "erroneous" file where the error is occurring? It is such a simple formula so it is difficult to understand why it does work.

I know it is very basic but check the cell has no extraneous blanks i.e " ON HOLD"

Try this

=IF(ISBLANK(C4),"N/A",IF(C4="open",TODAY()-E4,IF(TRIM(C4)="ON HOLD","ON HOLD","CLOSED")))

Hello All,

I am very happy to say that John Topley, you TRIM function addition works a charm. Thank you very much. Unfortunately, I can't really send a fragment of the document due to it's confidential nature.

ChemistB,
I did a "Len" test on both cells in the document, the cell from row D in my main document returned a length of 8, while the other "On Hold" returned a 7 as to be expected.
There must be a hidden character in there somewhere. I'm unsure where and It's for sure not visible to the standard user. I have re-typed "On Hold" into both of those cells countless times.

I had given up on this formula, thanks for sticking with this thread!