Formula Functions causing cell references limit error?

mtp105

Hello,

I'm trying to determine which FUNCTIONS I'm using in column formulas of a program schedule sheet that may be causing too many cell references so that I'm going over the limit of 25 million references (when I have around 3000-4000 rows). Most of my formulas just have @row formulas, but I don't know what some of these FUNCTIONS are doing behind the scenes.

Column formulas that I'm using:

Successors  =JOIN(SUCCESSORS(Tasks@row), ", ")

Level  =COUNT(ANCESTORS(Tasks@row))

Late =IF(AND(Status@row <> "Complete", ISDATE(Finish@row), Finish@row < TODAY()), 1, 0)

On Critical Path  =IF(ISCRITICAL(Tasks@row), 1, 0)

%Complete  =IFERROR(IF(Status@row = "Complete", 1, IF(Status@row = "Not Started", 0, IF(Status@row = "In Progress", MAX(0, MIN((TODAY() - Start@row) / (Finish@row - Start@row)))))), "")

Phase  =IF(Level@row = 2, Tasks@row, PARENT())

Deliverable  =IF(Level@row = 3, Tasks@row, PARENT())

Project  =IF(Level@row = 1, Tasks@row, PARENT())

Here's the error I get:

I suspect the ISCRITICAL function is scanning info on all rows multiple times to identify the critical path since deleting this column caused the sheet to respond much quicker and without the error. But I'm not sure if there are other functions I'm using that cause as many or more cell references. I think I can live without the critical path column (though it would be nice since the critical path in the Gantt chart isn't very easy to see when you have a couple thousand rows).

Does the SUCCESSORS formula scan all the rows multiple times too? I really need that one to try to track what's pushing the critical path since Smartsheet does not have an Inspect functionality or a built in Successors column like MS Project.

And what does ANCESTORS do? Is it also scanning all rows? I assumed it was just counting the number of intents @row.

My Late and %Complete calculations are just @row references. So even with 4000 rows and the number of cell references in them, I have less than 72,000 cell references - nowhere near 25 million!

My projects are quite large and complicated, and splitting them into multiple sheets (as suggested by tech support) is not a good option since I have multiple team members working in the schedules that are not very experienced or skilled in Smartsheet, and I do not have time to manage the issues this would cause. Ideally I need to just point them to one place to do their project status updates. And I need all the formula columns for the detailed reporting I need to do on these high visibility programs.

It would be nice if Smartsheet Development could improve the Successors and Critical Path tools to at least be equivalent to what MS Project offers. I already get pushback on using Smartsheet from diehard MS Project and Primavera P6 users that do not like the lack of visibility to this info (I don't like it either), hence the need for these column formulas that are then causing this sheet error issue.

Any insights on these FUNCTIONS and formulas would be much appreciated!