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!

che.rabajante

Hello @mtp105

FYI.. You’re running into Smartsheet’s formula reference limit, likely due to the cumulative effect of formulas across thousands of rows. Here are some formulas that can contribute significantly to the total reference count:

Potentially Intensive Formulas
JOIN(SUCCESSORS) and COUNT(ANCESTORS): These functions can generate numerous references, especially if tasks have many successors or ancestors. Each row reference adds to the total count.

ISCRITICAL: Determining critical paths can be resource-intensive, particularly when Smartsheet needs to analyze many task dependencies.

%Complete Formula: This formula includes nested IF conditions and references both TODAY() and other date fields. The dynamic nature of TODAY() requires frequent recalculations, which increases the reference count.

Phase, Deliverable, and Project Formulas Using PARENT(): While simpler, these formulas still reference parent rows, adding to the load, especially in complex hierarchies.

Note:
Formulas that produce errors still count toward Smartsheet’s 25-million cell reference limit. Even if you correct an error and get the intended result, the referenced cells remain included in Smartsheet’s calculation.

I've frequently encountered this issue when using the Gantt view in Smartsheet for managing complex project schedules. Smartsheet is generally best for basic task scheduling—it’s not ideal for complex projects, as it doesn’t have the capabilities of dedicated project management tools like MS Project or Primavera P6.🙂

Thank you!

che

mtp105

Hi Che,

Thanks for your reply. I'm not sure it really answers my question other than reiterating what I already said regarding suspecting the Functions I used in some of my formulas. I'd like to understand better where the actual "cell references" are coming from and how they are used by the formula functions. It seems that you're saying the limit is not just cell references, as the error message suggests, but is the number of calculations in general that Smartsheet is making. Is this correct? Do you work for Smartsheet and know how these functions are programmed, or are you speculating based on experience with using it? I'd like to understand if the error message is really just about cell references or number of calculations. These two seem different to me from a literal coding perspective.

If Smartsheet can't handle complex projects, they may need to change their sales pitch and advertising to high tech companies that have more than just "basic task scheduling".

Georgie

Hi @mtp105,

Thanks for providing all the column formulas you’re using. 

I can confirm that project functions such as ISCRITICAL and SUCCESSORS will always reference 3 entire columns each, even when @row is used. This is because they need to look at the entire Predecessors, Start Date, and End Date columns in order to calculate. These are therefore likely to be the main reason you’re reaching the cell reference limit. 

As you’ve mentioned that you can live without the Critical Path column, I’d suggest doing that to avoid reaching the limit. 

Alternatively, I understand that splitting the sheets might not work for you, but would you be able to split the sheet into multiple sheets and then create a row report to bring some of the columns from the separate sheets into one place? You could also filter the report as desired - then you can share the report to team members for them to make updates there. 

Hope that helps!

Georgie

mtp105

Hi Georgie,

Thanks for your feedback. I did end up deleting the ISCRITICAL column and it's functioning much better. It's too bad we need to lose this functionality since its important to understand the impact of changes to the schedule, and it's hard to see critical path with such a large schedule. Same with the Successors, we really need to be able to see the impact of timing changes and task delays, so having Successors is a good way to look ahead in the schedule to see what's happening or what will be impacted. With a few thousand rows, it's hard to keep it all in our heads! I often get asked what's driving certain dates, so I need a way do work backwards and see what's going on since SS doesn't have the Inspect tool like in MS Project.

I had already split this part of the project into two sheets since I wasn't able to add all the rows I needed due to the error. Dealing with it through reports isn't the best option since the project team members are doing their own task updates and need to edit the schedule a couple times a week (on average) each. While I understand you can do some updates through reports, it doesn't allow for all the changes the team members need to make, such as changing Start dates when there's a predecessor, changing finish dates directly, or deleting or adding tasks, which happens often in a very dynamic project.

I've already submitted a request that Smartsheet change the functionality that automatically deletes predecessors if you change or delete a start date with predecessors, but not sure if/when it will be implemented. It is a frequent situation on projects that a task with a predecessor does not start as soon as the predecessor is finished, especially if it's not on the critical path (which we can't see very well in Smartsheet, especially without the ISCRITICAL column and two different sheets to cover the whole project). But we need to see the real status of the project tasks, so we ask that team members update the dates/durations to reflect what they're actually doing, showing when they expect things to start and finish. A simple change to the Status field or a Duration can be made through a report, but it doesn't show the updated Finish date until you save the report and refresh it. So it's not easy for team members to do this when they have multiple updates to make and be able to see their impact. A report also does not show the indentation project structure. You can group rows in a report, but then they are ordered alphabetically or by date, which is not the order things happen on projects or a reflection of what the actual project schedule looks like. We do use plenty of reports, but to show things like late tasks, upcoming tasks, and other filters. It's not the ideal way to do project schedule updates, which is the main reason for having these schedule sheets in the first place.

This program already has project schedules on 6 different sheets, all tied together into a timeline sheet (#7) in order to show and manage the whole program, so these system and functionality limitations make things harder. The reason we use Smartsheet as opposed to MS Project or Primavera P6 is so that team members and stakeholders can see what's happening, see what they're supposed to do, do their own updates, and see the impact of changes and program status across multiple schedule sheets, reports, and dashboards. I then integrate the Smartsheet files with SharePoint pages (button links to editable or read-only (published) files, embedded files, etc), which works great to help keep everyone, especially stakeholders, informed (except it would be nice if the new Timeline view had an embed code… hopefully coming from SS someday…). And it's also really nice to be able to link task from multiple project schedules together (though it's a cumbersome and slow process to set it up) so all the different parts of the program can automatically update each other. (I know this functionality is also available with MS Project (enterprise version - does this still exist?) and Primavera P6, but no one can see it except the project manager or other paid subscribers, so what good does that do? And they require paid licenses for all users so that's not desirable either.

I guess Smartsheet's reporting, accessibility, and visibility features outweigh the difficulties of lack of scheduling functionality since it's really just me as the project manager that's inconvenienced. But it sure would be nice to have some of these features enhanced or fixed to make life easier, and to improve adoption of the tool for other non-Smartsheet experienced project managers. And it is also helpful to understand what's really happening in the coding so I know how to avoid or work with the limitations - hence my original post.

Thanks to everyone on the Community for chipping in with their experiences and knowledge. This is the most helpful support part of Smartsheet.