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

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…