Formula References vs Lengthy Formula Updates

Ariana Arden

I maintain a lot of large sheets - at least 9 per FY with around 190 columns and up to 1,500 rows per sheet.

Within these are many transit timeline formulas for off-shore shipments. These are often in flux, and when something changes, I need to go through each sheet and update the numbers in the column formulas. This can take a while and be a pain.

What I am considering is instead having a separate reference sheet for the numbers. The transit formulas would instead lookup to that sheet and when updates need to happen, it would just be a matter of updating in one place.

Would that work? I am concerned that this would either be too many cell references per sheet (I remember this has been an issue in past, not sure if it still is), or that it would slow down the processing time to a crawl. Due to the size of the sheets, loading them and the reports connected to them can already take a while.

Jeff Reisman

@Ariana Arden

Having your formulas reference lookup values from a single source can certainly make you more efficient.

You are correct that you need to think about the number of inbound links. From my experience though, it has more to do with the number of rows in the data source. For example, I have a sheet I use to pull values from a large sheet - 90 columns, 3500 rows. I have references to 19 of those columns, and Smartsheet considers every cell in each of those 19 columns to be an inbound link. So my sheet has 66,500 inbound links, while the limit of inbound links is 100,000.

Now, since you are going to pulling inbound links from a smaller sheet into a larger sheet, you can create a lot more column range references in your larger sheet.

Ariana Arden

@Jeff Reisman Interesting, I didn't know that. Thank you for the info!