Sheet limits and column formulas

CycleBagEd

Hi all,

Sheet limits suggest 100,000 cross sheet referenced cells

Does a column formula (referencing a single cell on another sheet) count as a single cross reference or is it multiplied by the number of rows on the inbound sheet?

Also, are these limits strictly sheet limits, or will the limitations cascade to other sheets? For example, if sheet "A" references 11 "B" sheets via 11 cells, and each "B" sheet references "C" sheets via 10,000 cells each, will sheet "A" be able to to this on not because ultimately, 110,000 references are at the end of the chain?

Or is it ok, because no single sheet exceeded the 100,000 limit?

thanks

Ed

Genevieve P.

Hi @CycleBagEd

Yes, you are correct! When you reference an entire column, as rows are added to that column then the cell references increase since there are new cells in that column.

You are also correct that Reports cannot be referenced by formulas; you would need to reference the underlying sheet(s) to pull that data through a formula.

Cheers,

Genevieve

Genevieve P.

Hi @CycleBagEd

If you're only referencing one cell in your {range}, then that one cell counts as 1 referenced cell, even if you have that formula multiple times in your sheet. Paul has a good discussion about referenced cells in this other Community post.

Keep in mind though, this limit is about the number of cells referenced, not the number of cells that have data in that reference. If you've selected an entire column but you're only returning the one cell at the top, the entire column and all of its cells are still "referenced" and will count towards this limit.

Then in your second example, no, the limit would not "cascade". If you're referencing 11 cells in Sheet A's formula, then that sheet would have 11 cells referenced (even if those cells contain formulas and reference somewhere else).

As another alternative, you could use a Report to bring together multiple sheets into one view if you wanted to break out your formulas into different sheets but have them visible in one place.

Cheers,

Genevieve

CycleBagEd

Thanks @Genevieve P. that's very useful.

I assume that formulas which reference a full column (like one would commonly do with index/match) then this range is controlled by the number of rows on the sheet - which can expand due to creation of new rows. I mention this because Excel would technically be referencing the whole column of a million or so rows in such a formula (albeit I understand that Excel is able to exclude the empty chunks of sheet from processing to keep things speedy). So if I use index/match on 2 columns on a 60 row sheet, this is 120 references, but as soon as that becomes an 80 row sheet, it is now 160 references.

I am using reports where I can to gather data but it's not possible to combine data from e.g. sheet type A with dataset B on a report itself, which only gives me the option of trying to get dataset B into the rows of sheet type A via lookups and such if I wish to collate data in full on a report.

I think if we were able to reference reports as a data source for sheet reporting, that would be most useful - that is to say, gather sheets into a report, then use that report as a data source itself for reporting from a sheet. This doesn't seem possible on Smartsheet itself.

thanks

Ed

Genevieve P.

Hi @CycleBagEd

Yes, you are correct! When you reference an entire column, as rows are added to that column then the cell references increase since there are new cells in that column.

You are also correct that Reports cannot be referenced by formulas; you would need to reference the underlying sheet(s) to pull that data through a formula.

Cheers,

Genevieve