Cross-sheet Formula Limitations and Possible Work Arounds
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?
Help Article Resources
Check out the Formula Handbook template!