Data Matrix for dashboard - cross sheet formulas

I have created extensive data matrix's to calculate # of Jobs and dollar amount for individuals in our company to track capacity. However, as our sheets get larger, the formulas wont calculate. I receive an error message reading:

"Some cross-sheet formulas cant be updated, because this sheet has more cells referenced from other sheets than the maximum allowed (25000)"

Currently I have 3 separate data matrix's to calculate this needed information on our dashboard. is there a way to get around this or will the limit on cells referenced ever be increased? I am getting "#Calculating" in all these cells.

Find more posts tagged with

Workspaces

Manufacturing

Construction

Sheets

Hospitality

Healthcare

Finance

Formulas

Reports

Project Management

Comments

Paul Newcome

The only way around this that I can think of is to spread out the data in your matrix(s) across multiple sheets and then compile that data into your "Master Matrix".

pkitzens

That is what i have been doing but before i know it i will have almost 5 matrixs. Could the master sheet that the matrixs are ran off of be the issue if it is getting too large?

Paul Newcome

Very much so. If you have 10 different ranges referencing the Master Sheet and 3 matrices, then every row you add to the master sheet is 30 additional cell links.

You may need to find some common criteria and use helper columns (I tend to lean towards checkboxes) on the Master sheet. You can then use IF statements to check boxes based on certain criteria and just reference the checkbox column(s) in your cross sheet formulas. JOIN statements are also useful in helper columns when looking at multiple sets of similar criteria.

Instead of looking at one range for the Project Lead, another range for the 1st Assistant and yet a third range for a 2nd Assistant just to see if one name is listed in any of those three ranges, you could use a simple JOIN statement to pull all three of those columns together into one.

Now you only have one range to look at for your cross sheet reference and can use a FIND("text", @cell) > 0 as your criteria to search the entire string from the JOIN function for one particular name.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…