# Cross-sheet Formula Limitations and Possible Work Arounds

Options
✭✭✭✭

First, what does the term "distinct cross-sheet reference" mean?

I've just run into a message that I've exceeded the 100 distinct cross-sheet references and am looking for a possible workaround.

Background: We have three sheets with various information about our employees. Basically, one sheet is the Employee Master List. Another is a list of the projects they have worked on with a simple Job # and "Yes" in the cell if they've worked on that particular project. And finally, the last sheet tracks various safety training & certification information on the employees. They all tie together through an employee ID #.

Some have suggested we break down the Safety Sheet into only 100 record rows from ~1,000.

I don't think this is practical given that it is currently being used to track over a thousand employee rows (active & inactive).

I cannot see us breaking it down into ten or more separate sheets and then trying to figure out a way to pull that information back together in a report.

One idea I had would be to use automation to simply copy the data into cells as a value instead of using a cross-sheet reference formula. But am a little concerned with that approach.

Does someone have an alternative on how to approach this?

Thanks.

Bert

Tags:

• ✭✭✭✭✭✭
Options

I am very curious about how you have your sheets setup. Your description leads me to believe on a small number of cross sheet references would be necessary. In which of the three sheets are you encountering the error?

• ✭✭✭✭
Options

Carson,

On the Safety Sheet.

The formula is listed below. There is not a problem with the formula as far as I can tell because I have a bunch of others exactly like it, just pulling from a different range column (i.e. 175, 180, etc.)

=INDEX({ProjectWorkedOn Range 189}, MATCH(EEID@row, {ProjectWorkedOn EE#}, 0))

The Employee Master file uses this Formula

=INDEX({ProjectWorkedOn Range 189}, MATCH(Primary@row, {ProjectWorkedOn Range Primary}, 0))

And the ProjectWorkedOn sheet basically just has an EE number, Full Name, and about 200 columns with their three-digit Project/Job # (i.e. 189).

• ✭✭✭✭✭✭
Options

So your projects sheet has approximately 200 columns, and your safety sheet has a reference to each of those columns?

• ✭✭✭✭
Options

Yes, that is correct.

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

A distinct range is each with a unique name. Depending on security, you could use a VLOOKUP formula instead.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

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

Did my post(s) help or answer your question or solve your problem? Please support the Community 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!

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!