Unable to create cross-sheet references

lcamacho19911

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

Andrée Starå

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

Sam Hilsman

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.

Chris Shifflett

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!

DWise

https://community.smartsheet.com/discussion/comment/266430#Comment_266430

I often hit this limit ... here's my work-around:

1. Run your Charts & Metrics Sheet up to the limit of 100.
2. Save as New, multiple times.
3. Parse things out into smaller chunks.
4. 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!

PeggyLang

@DWise

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.

Nilesh

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.

Genevieve P.

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

Nilesh

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.

Genevieve P.

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?

Nilesh

That perfectly worked. Thank you so much Genevieve :)

JennaD

https://community.smartsheet.com/discussion/comment/266430#Comment_266430

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!