is there a limit to how many nested IF statements a formula can contain

mifala.chris

See my formula below, I have 14490 items in [Shakedown List] and 6835 in [Exto List] i need to compare and find the matches using the formula in [Test Results]. The Column [Unique Identifiers] is for my reference to see if I have added all possible matches to the formula. So far I have not gotten half way through my [Unique Identifiers] and can no longer add one to the Formula.

Is this caused by Cell reference limit of an error in my formula, and if cell reference limit how do I overcome this?

Formula

=IFERROR(IF(CONTAINS("0.5", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 11), @cell)), 1), IF(HAS([Shakedown List]@row, "9%"), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 7), @cell)), 1), IF(CONTAINS("10GeH4H2", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 14), @cell)), 1), IF(CONTAINS("AF261", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("AF18", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("ASH", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 10), @cell)), 1), IF(CONTAINS("AH104", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("LDCZ", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 6), @cell)), 1), IF(CONTAINS("AIT", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 6), @cell)), 1), IF(CONTAINS("B-DHF", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 12), @cell)), 1), IF(CONTAINS("ACV", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("AXN", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("ACT", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("FAXN", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 7), @cell)), 1), IF(CONTAINS("DNX", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 7), @cell)), 1), IF(CONTAINS("AX", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(MID([Shakedown List]@row, 5, 4), @cell)), 1), IF(CONTAINS("BATZ", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(MID([Shakedown List]@row, 2, 8), @cell)), 1), IF(CONTAINS("DDS", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 12), @cell)), 1), IF(CONTAINS("HEPAR", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 8), @cell)), 1), IF(CONTAINS("BKRD", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 6), @cell)), 1), IF(CONTAINS("B2H6H2", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 4), @cell)), 1), IF(CONTAINS("BDCS", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 13), @cell)), 1), IF(CONTAINS("BETAP", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(LEFT([Shakedown List]@row, 8), @cell)), 1), IF(CONTAINS("BCX", [Shakedown List]@row), INDEX(COLLECT([Exto List]:[Exto List], [Exto List]:[Exto List], CONTAINS(RIGHT([Shakedown List]@row, 12), @cell)), 1), "Messy")))))))))))))))))))))))), "")

Genevieve P.

Hi @mifala.chris

There's a character count limit in a cell of 4,000 characters. (See: System requirements and guidelines for using Smartsheet). Your formula is currently 3,903 characters.

One quick and easy way to simplify this is to change your column names to be two letters, no spaces. This will remove every instance of [square brackets], as well as all the other characters in the name.

For example, it would change [Exto List]:[Exto List] to be EL:EL, changing 24 characters to be 6. If you did EL and SL, it would change your total character count from 3,903 to 2,365 so you could add more.

Changing the column names in the sheet will update the formula automatically.

I haven't actually looked at your formula to see if there's a different way to do this. Can you clarify exactly what your use-case is, and what you want to gain from the formula?

Thanks!

Genevieve

mifala.chris

Thanks Genevieve P I will try that. We have two separate systems that store information differently and the need is to reconcile the two to confirm all critical items are captured for testing.

Chris