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!
