Too many formula references
I've got a page that's running into an error for having too many cells referenced. The problem is that the page is a list of samples, where samples are grouped into lots (40 samples per lot). I need to do a lot of comparisons within a given lot, which means I'm constantly using formulas that group those lots, such as:
=MAX(COLLECT(SampleNum:SampleNum, [Lot]:[Lot], [Lot]@row, Reviewer:Reviewer, Reviewer@row, Date:Date, Date@row))
Because there are about a thousand samples, and growing, it doesn't take long before these lookups reference a lot of cells.
1.) Would the order of this example formula reduce the number of references that Smartsheet counts against me? For instance if the match for "Reviewer:Reviewer, Reviewer@row" is 100 samples, and "Lot:Lot, Lot@row" is 40 samples, and "Date:Date, Date@row" is 3 samples, would it make sense to arrange the formula order so Date was the first criteria, Lot was the second, and Reviewer was the third? I'm not talking about processor burden, I'm talking about Smartsheet cell count, which I think is some kind estimate of theoretical maximum.
2.) When a lot is complete, I don't need to perform calculations on it anymore, and I have a simple True/False flag on every sample row to indicate if the overarching lot is complete. Would adding an IF statement to shut the calculations down if the lot is complete reduce the number of references Smartsheet counts against me? For instances:
=IF([Complete?]@row="True", "", MAX(COLLECT(SampleNum:SampleNum, [Lot]:[Lot], [Lot]@row, Reviewer:Reviewer, Reviewer@row, Date:Date, Date@row)))
3.) Within the sheet, the samples are all children of the main lot--I've tried to figure out a way to only reference other children within a parent group using PARENT and CHILD, but it feels like I need something more akin to a "SIBLING" reference. If this existed, I could arrange the original calculation as:
=MAX(COLLECT(SIBLING(SampleNum@row), SIBLING(Reviewer@row), Reviewer@row, SIBLING(Date@row), Date@row)
I tried using a nested statement to get the same effect, i.e. CHILDREN(PARENT(SampleNum@row)), but no dice. Any way to perform calcs on sibling rows, other than my method of "LOT:LOT, LOT@row"? It feels like being able to reference siblings would reduce my referenced cells dramatically.
Thanks for any thoughts and input!