Cross references confuse me a bit

Chinaflor

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! 🙂

Paul McGuinness

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