Cross Sheet Reference Maximum Limitation


Hello - I learned the hard way unfortunately that there is a maximum number of cross sheet references you can have in any one sheet, which is 100. I'm wondering if anyone can help me come up with a viable alternative solution to accomplish what I'm trying to do then, to bypass the 100 max cross sheet reference limitation. I have a source sheet where I'm storing all my data. I then created a separate sheet for calculations that are based off of that source sheet. Now, I've created a third, separate sheet to use formulas that use cross sheet references to pull data from the calculations sheet. My goal is to have one, single source of truth which will be my source data sheet, so if any changes come up, I am only updating in one place: the data source sheet. However, I am trying to create these other sheets that use data from the source sheet to show the data in multiple different ways, so that my dashboard has various views of the data. All said, my third sheet is using cross sheet references with a formula to denote a checkmark when something from that calculations sheet is true and blank when it is not true. I'm showing which programs are offered at each of our campuses and there is a cross sheet reference for each individual cell so that my checkmarked sheet will auto-update when i modify my source data sheet (ie we add a program at a campus, make the checkmark appear; we deactivate a program at a campus, make the checkmark disappear). Because we have so many campuses (going across the top) and programs (going along the vertical left hand side of my checkmark sheet), and each cell requires a cross sheet reference, I am well beyond 100 unique cross sheet references. I'm including some images to help illustrate what I'm trying to do. Anyone else out there have ideas for workarounds without making this too messy? My overall objective as the owner of this data is to only have to update changes in ONE place, while the differently formatted views just auto update based on any changes I make to the source data so I avoid user error, version control, etc. Thank you in advance!