Cross-sheet Formula Limitations and Possible Work Arounds

Bert Hoffbeck

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

Carson Penticuff

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?

Bert Hoffbeck

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).

Carson Penticuff

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

Bert Hoffbeck

Yes, that is correct.

Andrée Starå

Hi @Bert Hoffbeck

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!