Unable to create cross-sheet references
The system shows a message "Unable to create cross-sheet references because this sheet already contains the maximum number of different cross sheet references" . How many references we can do? I am using a sheet that has information of 13 sheets combine. (like a summary sheet). I like the summary sheet but the information in listing down and in my case I need it in a grid sheet.
Luis from PR
Comments
-
Hi Luis,
You can have 100 distinct - cross-sheet references.
More info: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas
Before You Begin
Here are some things to keep in mind as you work with cross-sheet formulas:
- All the data in the range being referenced is available and potentially visible in the destination sheet, meaning you shouldn’t include any data in the reference that you don’t want available in the destination sheet.
- Each sheet can include no more than 100 distinct cross-sheet references.
- There’s a limit of 25,000 inbound cells that can be referenced from other sheets into one sheet in total.
- When you create a reference to another sheet, you can only reference data from a single sheet. If you need to reference data from multiple sheets, you’ll need to create multiple references using the steps below
- The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. For more information on this and other formula errors, see Formula Error Messages.
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
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.
-
This is the most ridiculous limitation possible.
I just spent over 6 hours creating a massive dashboard for an extremely important project, and have run into the 100 cross-sheet references limitation with the need to create 8 more.
I cannot believe they tell you that the best practice in their system is to create a metric sheet for a dashboard and them limit you to 100 cross-sheet references. If this is not fixed ASAP then this product is essentially unusable for any enterprise level projects.
-
Sam,
I completely agree with you and your frustration. I build at an enterprise level as well and this limitation is forcing me to look for other solutions. Smartsheet has a lot of features, but still very elementary IMO.
Thanks!
-
I often hit this limit ... here's my work-around:
- Run your Charts & Metrics Sheet up to the limit of 100.
- Save as New, multiple times.
- Parse things out into smaller chunks.
- Store collection of newly created Charts & Metric sheets in a Charts & Metrics folder.
Albeit, a pain if you're not planning for this from the beginning!
-
THANK YOU for this. YES definitely a PAIN when not planned for in the beginning!!!
I need a mental break and then when I come back I will tackle splitting my metrics sheet up to accomodate.
-
Hello All,
i have a similar issue. i have to create a sheet reference.
1.master sheet where i have to create formula.
2.sheet 2 has the dat we need to populate to master sheet.
when i am trying to create formula and refer to another sheet window, i cant find that sheet (i have admin access to that sheet)
please suggest.
-
Hi @Nilesh
Is it possible the sheet was renamed or created recently? It can take up to 10 minutes for the pop-up window to properly index new sheets or new names.
I would suggest waiting a few minutes and trying again - did that help?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I tried that. i just removed text what i was searching in screenshot. i am getting no matches found or getting list of sheets but i cant see the one i am looking for.
-
Hey @Nilesh
At this point, instead of searching for the name I would use the Sheet List Navigation.
First confirm where the second sheet is located (or even create a Folder and put it there to make it easy to find). Then click on the little "sheet" icon next to the Search icon to find it that way:
You can link in from sheets where you have at minimum Viewer Permission, so Admin Permissions are definitely enough.
If you still can't find it that way, can you confirm that you still have permissions on the sheet? Is it possible that this sheet was either deleted, or your access removed?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That perfectly worked. Thank you so much Genevieve :)
-
Same here and it's 2024 - years after this initial discussion was brought up (2020). Was hoping there was a better solution / improvement to this at this point bc I too just spent several hours developing sheets, reports and a dashboard and it is not reasonable, feasible or practical to parse out my source data into multiple sheets just to work around the cross sheet reference limitation. Finding that Excel actually has the ability to do what I'm trying to do for this particular effort and can do it MUCH more simply. This is super frustrating! If anyone has an update to this, I'd be thrilled to hear about it. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!