Data Matrix for dashboard - cross sheet formulas

pkitzens

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. 

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.