Hello,
I was stymied today trying to make cross-references between sheets. See the attached screenshot of the notice. I need to work around this - I have 13 more sheets to go that will need the same treatment.
Your answer? Don't use as many cross-sheet references. You are asking Smartsheet to do something beyond its current capacity. A sheet can include no more than 100 distinct cross-sheet references.
Depending on how your data are structured and what you're trying to accomplish, you might be able to use an intermediary sheet - from your root data to an KPI sheet to minimize duplications. Or you might be able to strategically pull reports together to allow you to see the data you're using.
If "Yeah, don't do that" is an answer that won't work and you absolutely must use a huge amount of cross-references, you may wish to consider looking into Data Mesh. The premium app allows for automations to build that perform a similar logic to cross-sheet references, but do so with an entirely different mechanism. The result is similar to an "Update Cell" automation, in that the "reference" isn't a formula, but a field that CAN be overtyped if you set up the sheet accordingly. It gives a lot of flexibility - and doesn't have the same kind of capacity issues it seems you are running into.
Good luck!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Hi,
I hope you're well and safe!
Do you want to connect all the 13 sheets to this one?
What formulas are you using? VLOOKUP, INDEX/MATCH, or something else?
I hope that helps!
Be safe, and have a fantastic day!
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 ⬆️ - Awesome ❤️ - 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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Hello Andree,
Let me clarify first - I am working with the basics of SS, building my own PTO tracking sheets for 18 different accounts. I am researching RM as a possible upgrade but having troubles with our university sharing (everything moves at glacier speed here). In the mean time I have to come up with a solution.
I have access to Data Shuttle and Data Mesh. My core data comes from a financial report I have to manually download from source and update to each sheet every month (I tried Data Shuttle, but it does not work consistently, so manual works right now).
Once I have these done, I should be able to pull the reports I need to do staff projections. I have two areas in each sheet I need to cross reference 1) to pull historic data by year to get a total spent per person and compare to time allotted to determine time left to spend (this can be 5+ years 6-10 staff, column for each year necessary) and 2) pulling current and projected raises in salary data per person where we are projecting time to be spent until account(s) are empty (similar data and columns depends on how long the account lasts) - this part is what we will be spending most time adjusting.
I am pulling data from several sheets - 1) Staff Salary: includes staff list, current, historical and projected salary, 2) Historic/Projections salary spending sheet per account: set up by fiscal year reflects overall spending for each staff and overall account by year, 3) Financial tracker sheet per account: summary of spending by areas of spending (ie salary, travel, materials) - this gives me the balance to spend when projecting.
Here are samples of the formulas I am using:
=SUM({SFY25_ALD} * SUM([Jan25]@row:[JUN25]@row)): Pulls salary by assigned time/period times the total of projected time.
=SUM({0182__FY23_Beeton} / {0182_FY23S_Beeton} * 12): Pulls historical data from the historic/projection sheet to give the amount spent per year per person. Allowing another column to formula to figure for the time left to spend.
I suspect I am using an inefficient formula to pull this data creating my cross-reference problem- I probably should be using a reference to search by name and match to data.
