Cross Sheet Reference Maximum Limitation

JennaD

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!

Smartsheet Cross Sheet Reference Limitation question.docx

Paul Newcome

Have you thought about using row reports to filter the visible data?

Andrée Starå

https://community.smartsheet.com/discussion/123185/cross-sheet-reference-maximum-limitation

Hi,

I hope you're well and safe!

Can you elaborate on why you're using a reference per cell?

Can you share some screenshots of some of the references? (Please delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!