# How can I work around the Formula Cell Reference Limit?

✭✭✭✭

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!

Tags:

• ✭✭✭✭

I figured it out 🙃

• ✭✭✭✭

I figured it out 🙃

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

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.

• ✭✭✭✭✭✭

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.

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!