How can I work around the Formula Cell Reference Limit?

Ariana Arden

Hi, I'm getting the below error message. My needs are too complicated to convert any of the formulas to automations and I'm not sure how to simplify.

Here is essentially what I am trying to do - every row is a different item and columns A, B, C, and D contain fabric codes. In columns A1 and A2 (and B1, B2, C1, etc.) I need to identify whether a test is needed on this item; however, a test is only needed once per fabric, and fabrics can be used on multiple items. I was able to create successful but convoluted formulas - It looks at all of column A and picks only the first of each unique fabric code to say that Yes a test is needed. For column B, it looks at column A and then if the fabric code is not there, it does the same and picks only the first of each unique code in column B to say Yes to, and so on for column C and D.

These work, but I have no idea how to simplify these formulas so that I can still get the result I need and not exceed the formula cell reference limit.

My formula for columns A1&2: =IF(COUNTIF([Fabric 1- Code]:[Fabric 1- Code], [Fabric 1- Code]@row) = 1, "Yes", IF(WFX@row = INDEX(WFX:WFX, MATCH([Fabric 1- Code]@row, [Fabric 1- Code]:[Fabric 1- Code], 0)), "Yes", "Applied"))

And then for D: =IF([Fabric 4- Code]@row = "N/A", "Not Needed", IF(VLOOKUP([Fabric 4- Code]@row, [Fabric 4- Code]:[AATCC 20/20A- FIBER CONTENT - 1 - Test], 13, false) = "Yes", "Applied", IF(VLOOKUP([Fabric 4- Code]@row, [Fabric 4- Code]:[AATCC 20/20A- FIBER CONTENT -2 - Test], 15, false) = "Yes", "Applied", IF(VLOOKUP([Fabric 4- Code]@row, [Fabric 4- Code]:[AATCC 20/20A- FIBER CONTENT - 3 - Test], 17, false) = "Yes", "Applied", IF(COUNTIF([Fabric 4- Code]:[Fabric 4- Code], [Fabric 4- Code]@row) = 1, "Yes", IF(WFX@row = INDEX(WFX:WFX, MATCH([Fabric 4- Code]@row, [Fabric 4- Code]:[Fabric 4- Code], 0)), "Yes", "Applied"))))))

As you can see if gets to be a bit much. Apologies, I know this is convoluted, but any help simplifying would be appreciated.

Thank you!

Ariana Arden

I figured it out 🙃

Ariana Arden

I figured it out 🙃

Andrée Starå

Hi @Ariana Arden

I hope you're well and safe!

Excellent! Glad you got it working!

Feel free to share any tips & tricks that you might have discovered.

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Ariana Arden

I was able to simplify the A formula to: =IF(WFX@row = INDEX(WFX:WFX, MATCH([Fabric 1- Code]@row, [Fabric 1- Code]:[Fabric 1- Code], 0)), "Yes", "Applied")

The D formula was simplified to: =IF([Fabric 4- Code]@row = "N/A", "Not Needed", IF((COUNTIF([Fabric 1- Code]:[Fabric 1- Code], [Fabric 4- Code]@row) + COUNTIF([Fabric 2- Code]:[Fabric 2- Code], [Fabric 4- Code]@row) + COUNTIF([Fabric 3- Code]:[Fabric 3- Code], [Fabric 4- Code]@row)) >= 1, "Applied", IF(WFX@row = INDEX(WFX:WFX, MATCH([Fabric 4- Code]@row, [Fabric 4- Code]:[Fabric 4- Code], 0)), "Yes", "Applied")))

With regards to the formula cell reference limit, I think it was the vlookups that were getting me in trouble.

Andrée Starå

@Ariana Arden

Excellent! Nicely done!

Yes, usually, it can be the VLOOKUPS that reference more than needed, so I, in most cases, recommend using INDEX/MATCH instead.