Updated formula returns blank??

damon.tackett

Good morning! I have a formula that I have been using successfully for quite a while. I made a small update to the formula and suddenly it won't return the requested info. I have been looking at it for an hour now and can't see what happened to change what is returned. If in my source column I put any other trigger from the formula it returns properly, but if i enter the new trigger it just returns a blank field. No error...just blank. (I apologize for the formula being so long, but it is really just the end of the formula that has the troublesome trigger)

Original Formula:

=IF(IF(CONTAINS("10ACO1GMPA", [OS98 Forms]@row), "10ACO1GMPA" + CHAR(10), "") + IF(CONTAINS("10G1CAH", [OS98 Forms]@row), "10G1CAH" + CHAR(10), "") + IF(CONTAINS("10G1CATM", [OS98 Forms]@row), "10G1CATM" + CHAR(10), "") + IF(CONTAINS("10G1CAVWPAS", [OS98 Forms]@row), "10G1CAVWPAS" + CHAR(10), "") + IF(CONTAINS("10G1CHPAS", [OS98 Forms]@row), "10G1CHPAS" + CHAR(10), "") + IF(CONTAINS("10G1CS", [OS98 Forms]@row), "10G1CS" + CHAR(10), "") + IF(CONTAINS("10G1CSM", [OS98 Forms]@row), "10G1CSM" + CHAR(10), "") + IF(CONTAINS("10G1GMA", [OS98 Forms]@row), "10G1GMA" + CHAR(10), "") + IF(CONTAINS("10G1GMCA", [OS98 Forms]@row), "10G1GMCA" + CHAR(10), "") + IF(CONTAINS("10G1H", [OS98 Forms]@row), "10G1H" + CHAR(10), "") + IF(CONTAINS("10G1INDP", [OS98 Forms]@row), "10G1INDP" + CHAR(10), "") + IF(CONTAINS("10G1MB", [OS98 Forms]@row), "10G1MB" + CHAR(10), "") + IF(CONTAINS("10G1TM", [OS98 Forms]@row), "10G1TM" + CHAR(10), "") + IF(CONTAINS("10G1VWPAS", [OS98 Forms]@row), "10G1VWPAS" + CHAR(10), "") + IF(CONTAINS("10G2CHPAS", [OS98 Forms]@row), "10G2CHPAS" + CHAR(10), "") + IF(CONTAINS("10GCAFORA", [OS98 Forms]@row), "10GCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCCAFORA", [OS98 Forms]@row), "10GCCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCFORA", [OS98 Forms]@row), "10GCFORA" + CHAR(10), "") + IF(CONTAINS("10GCO1CS", [OS98 Forms]@row), "10GCO1CS" + CHAR(10), "") + IF(CONTAINS("10GCOVW", [OS98 Forms]@row), "10GCOVW" + CHAR(10), "") + IF(CONTAINS("10GFORA", [OS98 Forms]@row), "10GFORA" + CHAR(10), "") + IF(CONTAINS("FGF", [OS98 Forms]@row), "FGF" + CHAR(10), "") + IF(CONTAINS("FGC", [OS98 Forms]@row), "FGC" + CHAR(10), "") + IF(CONTAINS("FGCF", [OS98 Forms]@row), "FGCF" + CHAR(10), "") + IF(CONTAINS("FGP", [OS98 Forms]@row), "FGP" + CHAR(10), "") <> "", IF(CONTAINS("10ACO1GMPA", [OS98 Forms]@row), "10ACO1GMPA" + CHAR(10), "") + IF(CONTAINS("10G1CAH", [OS98 Forms]@row), "10G1CAH" + CHAR(10), "") + IF(CONTAINS("10G1CATM", [OS98 Forms]@row), "10G1CATM" + CHAR(10), "") + IF(CONTAINS("10G1CAVWPAS", [OS98 Forms]@row), "10G1CAVWPAS" + CHAR(10), "") + IF(CONTAINS("10G1CHPAS", [OS98 Forms]@row), "10G1CHPAS" + CHAR(10), "") + IF(CONTAINS("10G1CS", [OS98 Forms]@row), "10G1CS" + CHAR(10), "") + IF(CONTAINS("10G1CSM", [OS98 Forms]@row), "10G1CSM" + CHAR(10), "") + IF(CONTAINS("10G1GMA", [OS98 Forms]@row), "10G1GMA" + CHAR(10), "") + IF(CONTAINS("10G1GMCA", [OS98 Forms]@row), "10G1GMCA" + CHAR(10), "") + IF(CONTAINS("10G1H", [OS98 Forms]@row), "10G1H" + CHAR(10), "") + IF(CONTAINS("10G1INDP", [OS98 Forms]@row), "10G1INDP" + CHAR(10), "") + IF(CONTAINS("10G1MB", [OS98 Forms]@row), "10G1MB" + CHAR(10), "") + IF(CONTAINS("10G1TM", [OS98 Forms]@row), "10G1TM" + CHAR(10), "") + IF(CONTAINS("10G1VWPAS", [OS98 Forms]@row), "10G1VWPAS" + CHAR(10), "") + IF(CONTAINS("10G2CHPAS", [OS98 Forms]@row), "10G2CHPAS" + CHAR(10), "") + IF(CONTAINS("10GCAFORA", [OS98 Forms]@row), "10GCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCCAFORA", [OS98 Forms]@row), "10GCCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCFORA", [OS98 Forms]@row), "10GCFORA" + CHAR(10), "") + IF(CONTAINS("10GCO1CS", [OS98 Forms]@row), "10GCO1CS" + CHAR(10), "") + IF(CONTAINS("10GCOVW", [OS98 Forms]@row), "10GCOVW" + CHAR(10), "") + IF(CONTAINS("10GFORA", [OS98 Forms]@row), "10GFORA" + CHAR(10), "") + IF(CONTAINS("FGF", [OS98 Forms]@row), "FGF" + CHAR(10), "") + IF(CONTAINS("FGC", [OS98 Forms]@row), "FGC" + CHAR(10), "") + IF(CONTAINS("FGCF", [OS98 Forms]@row), "FGCF" + CHAR(10), "") + IF(CONTAINS("FGP", [OS98 Forms]@row), "FGP" + CHAR(10), ""), "N/A")

Updated formula:

=IF(IF(CONTAINS("10ACO1GMPA", [OS98 Forms]@row), "10ACO1GMPA" + CHAR(10), "") + IF(CONTAINS("10G1CAH", [OS98 Forms]@row), "10G1CAH" + CHAR(10), "") + IF(CONTAINS("10G1CATM", [OS98 Forms]@row), "10G1CATM" + CHAR(10), "") + IF(CONTAINS("10G1CAVWPAS", [OS98 Forms]@row), "10G1CAVWPAS" + CHAR(10), "") + IF(CONTAINS("10G1CHPAS", [OS98 Forms]@row), "10G1CHPAS" + CHAR(10), "") + IF(CONTAINS("10G1CS", [OS98 Forms]@row), "10G1CS" + CHAR(10), "") + IF(CONTAINS("10G1CSM", [OS98 Forms]@row), "10G1CSM" + CHAR(10), "") + IF(CONTAINS("10G1GMA", [OS98 Forms]@row), "10G1GMA" + CHAR(10), "") + IF(CONTAINS("10G1GMCA", [OS98 Forms]@row), "10G1GMCA" + CHAR(10), "") + IF(CONTAINS("10G1H", [OS98 Forms]@row), "10G1H" + CHAR(10), "") + IF(CONTAINS("10G1INDP", [OS98 Forms]@row), "10G1INDP" + CHAR(10), "") + IF(CONTAINS("10G1MB", [OS98 Forms]@row), "10G1MB" + CHAR(10), "") + IF(CONTAINS("10G1TM", [OS98 Forms]@row), "10G1TM" + CHAR(10), "") + IF(CONTAINS("10G1VWPAS", [OS98 Forms]@row), "10G1VWPAS" + CHAR(10), "") + IF(CONTAINS("10G2CHPAS", [OS98 Forms]@row), "10G2CHPAS" + CHAR(10), "") + IF(CONTAINS("10GCAFORA", [OS98 Forms]@row), "10GCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCCAFORA", [OS98 Forms]@row), "10GCCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCFORA", [OS98 Forms]@row), "10GCFORA" + CHAR(10), "") + IF(CONTAINS("10GCO1CS", [OS98 Forms]@row), "10GCO1CS" + CHAR(10), "") + IF(CONTAINS("10GCOVW", [OS98 Forms]@row), "10GCOVW" + CHAR(10), "") + IF(CONTAINS("10GFORA", [OS98 Forms]@row), "10GFORA" + CHAR(10), "") + IF(CONTAINS("FGF", [OS98 Forms]@row), "FGF" + CHAR(10), "") + IF(CONTAINS("FGC", [OS98 Forms]@row), "FGC" + CHAR(10), "") + IF(CONTAINS("FGCF", [OS98 Forms]@row), "FGCF" + CHAR(10), "") + IF(CONTAINS("FGP", [OS98 Forms]@row), "FGP" + CHAR(10), "") <> "", IF(CONTAINS("10ACO1GMPA", [OS98 Forms]@row), "10ACO1GMPA" + CHAR(10), "") + IF(CONTAINS("10G1CAH", [OS98 Forms]@row), "10G1CAH" + CHAR(10), "") + IF(CONTAINS("10G1CATM", [OS98 Forms]@row), "10G1CATM" + CHAR(10), "") + IF(CONTAINS("10G1CAVWPAS", [OS98 Forms]@row), "10G1CAVWPAS" + CHAR(10), "") + IF(CONTAINS("10G1CHPAS", [OS98 Forms]@row), "10G1CHPAS" + CHAR(10), "") + IF(CONTAINS("10G1CS", [OS98 Forms]@row), "10G1CS" + CHAR(10), "") + IF(CONTAINS("10G1CSM", [OS98 Forms]@row), "10G1CSM" + CHAR(10), "") + IF(CONTAINS("10G1GMA", [OS98 Forms]@row), "10G1GMA" + CHAR(10), "") + IF(CONTAINS("10G1GMCA", [OS98 Forms]@row), "10G1GMCA" + CHAR(10), "") + IF(CONTAINS("10G1H", [OS98 Forms]@row), "10G1H" + CHAR(10), "") + IF(CONTAINS("10G1INDP", [OS98 Forms]@row), "10G1INDP" + CHAR(10), "") + IF(CONTAINS("10G1MB", [OS98 Forms]@row), "10G1MB" + CHAR(10), "") + IF(CONTAINS("10G1TM", [OS98 Forms]@row), "10G1TM" + CHAR(10), "") + IF(CONTAINS("10G1VWPAS", [OS98 Forms]@row), "10G1VWPAS" + CHAR(10), "") + IF(CONTAINS("10G2CHPAS", [OS98 Forms]@row), "10G2CHPAS" + CHAR(10), "") + IF(CONTAINS("10GCAFORA", [OS98 Forms]@row), "10GCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCCAFORA", [OS98 Forms]@row), "10GCCAFORA" + CHAR(10), "") + IF(CONTAINS("10GCFORA", [OS98 Forms]@row), "10GCFORA" + CHAR(10), "") + IF(CONTAINS("10GCO1CS", [OS98 Forms]@row), "10GCO1CS" + CHAR(10), "") + IF(CONTAINS("10GCOVW", [OS98 Forms]@row), "10GCOVW" + CHAR(10), "") + IF(CONTAINS("10GFORA", [OS98 Forms]@row), "10GFORA" + CHAR(10), "") + IF(CONTAINS("FGF", [OS98 Forms]@row), "FGF" + CHAR(10), "") + IF(CONTAINS("FGC", [OS98 Forms]@row), "FGC" + CHAR(10), "") + IF(CONTAINS("FGCF", [OS98 Forms]@row), "FGCF" + CHAR(10), "") + IF(CONTAINS("FGP", [OS98 Forms]@row), "FGP" + CHAR(10), "") + IF(CONTAINS("10G1CTCS", [OS98 Forms]@row), "10G1CTCS" + CHAR(10), ""), "N/A")

Paul Newcome

You forgot to add the new set to the original "logical statement" portion of the first IF.

You have

=IF(generated_string <> "", generated_string, "N/A")

You added the new IF/CONTAINS to the second generated_string but not the first.

Paul Newcome

You forgot to add the new set to the original "logical statement" portion of the first IF.

You have

=IF(generated_string <> "", generated_string, "N/A")

You added the new IF/CONTAINS to the second generated_string but not the first.

damon.tackett

@Paul Newcome of course! Thank you! I knew it was something simple, but the darn formula is so long i just couldn't see it.

Paul Newcome

Happy to help. 👍️