Cross references confuse me a bit
Back info, I have a running summary page that references different jobs and each job has its own sheet. So on my summary page I use a reference formula to pull the info I need. Where I am coming into a problem is I made a new job sheet and then I open my running summary page and used my reference formula and it pulls the new sheet up but then says it has a maximum number of cross references but the sheet itself is a new sheet.
Could I go on the job sheet and reference the summary? Would it do what I need that way? I would prefer to keep using the summary sheet as INBOUND (I hope I am understanding that correctly too, I was never an excel person).
Hope this made sense! 🙂
Answers
-
Hi @Chinaflor
We've all hit the maximum number of cross reference cells at some time its annoying but there are a few things you can do without needing to start over or make wholesale changes.
- Check the formulas you are using to reference the other sheet and make sure they are efficient as possible, i.e. are you using a vlookup to pull data? as a index / match formula would greatly reduce the amount of inbound cells. example - using vlookup on a range with 10 columns and a 1000 rows = 10000 cells being referenced. A index match formula doing the same task would reference only two columns so 2000 cells.
- Housekeeping, tidy the sheet, are there any old sheets that were being looked up against the summary sheet but you deleted? old jobs, test sheets etc. as deleting those old cell references if they still exist will free up cross cell reference space within the summary sheet.
- Are all the job sheets identical, column names etc. could you use a row report to pull the info you need, or could you create summary cells for the data you need and use a summary cell report to pull all those through?
I've found that some or all of these can help when this happens, hope that you can get it working.
Thanks
Paul
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!