Help with cross-sheet references

Ty Werven

I am having some trouble understanding this. I have a form connected to a large gathering sheet where individual peoples info is all tracked by a 'Unit' or team. I have automations set up to separate each units info from the form to that individual unit (So I have one form collecting 25ish teams form info, and then it gets separated out via an automation that separates it out by unit and send their responses to their unit sheet.)

Each unit has separate metric sheets that track responses to questions from their own unit sheet (just basic countifs formulas are being used tracking by dates and yes/no questions).

This info is used in Dashboards for the 25ish units.

Where is this "cross-sheet reference" error occurring in this process?

I have tried deleting some of the units to try and figure out where the problem may be stemming from but even that isnt working?

Jason P

Hi @Ty Werven

I believe Smartsheet allows 100 unique crosssheet references per sheet, so you’re likely hitting this with all the Metric Sheets even if;

• You only pull from one Unit Sheet to its own Metric Sheet.
• Each Metric Sheet likely has multiple separate crosssheet formulas and
• Each separate range you define counts as one reference even if they all pull from the same sheet.

Each distinct reference created via “Reference Another Sheet” counts toward the limit even if they’re from the same sheet

Example In a single Metric Sheet for one Team/Unit you might have:

• =COUNTIFS({Unit Sheet Date Column}, TODAY()) is Reference #1.
• =COUNTIFS({Unit Sheet Yes/No}, "Yes") is Reference #2.
• =COUNTIFS({Unit Sheet Date Column}, >= TODAY(-7)) is Reference #3.
• etc……

Do this 10 times per Metric Sheet across the 25 team/units! That’s easily 250 references across all Metric Sheets and Smartsheet will display the message you’re getting. Also , deleting other units doesn’t help if the Metric Sheet already has too many references on its own

Have a look at your crosssheet References, this will show you a list of all the named references your using, each one listed counts toward your 100 reference limit.

1. Open the sheet where you suspect the issue is occurring likely one of your Metric Sheets.
2. Click on any cell containing a formula with a reference e.g. COUNTIFS INDEX etc.
3. In the formula bar hover over or click on the blue Referenced Range text.
4. A panel will open on the right this is the Reference Manager.
5. At the bottom of this panel click Manage References.

Can you reduce references OR create One metric sheet that pulls in data? crosssheet formulas are great here but I’m not the guru on these but there are many in the community that are. Otherwise you will need to look at other methods of data gathering.