Cross-sheet references maxed out

Shawn Church

I'm building a large master reporting sheet that cross references multiple other form response sheets based on a shared "incident number". I have six more columns of data to pull over from another sheet but have maxed out the cross sheet references (100 per sheet!)

This master sheet then uses that data to create pdf's.

I don't think cell linking is the solution. As I understand it, cell linking is for defined or specific cells. The data I'm pulling over is from form responses and I need all cells in a column. I could be wrong - please let me know.

Moving/copying rows from one sheet to another doesn't seem to be a solution either as the data from each sheet matching the same "incident number" needs to be in the same row.

Does anyone know of a work around. All 100 cross-sheet references are INDEX/MATCH references. Is there a way to pull over multiple colums using fewer references???

Summary: I have 2 form response sheets and one database sheet that feed info into one large master sheet used to compile data and automate PDF's and emails.

Any thoughts?

Hollie Green

The only solution I can think of is to add some helper columns to your reference sheet and combine some columns and then split them back out on the destination sheet. That way you use fewer cross references. You can then hide the joined helper columns on each sheet.

Bassam Khalil

Hi @Shawn Church,

Could you please share a sample copy of your sheets after removing the sensitive data? It will help greatly in creating a more effective solution.

Shawn Church

@Bassam Khalil

Okay...Here you see my "master" sheet with an automated "Incident Number". This number is generated when a form is submitted from this sheet.

I have a serparate sheet ("sheet B") where additional info is submitted related to this specific "Incident Number". When info is keyed into sheet B with a matching incident number, that data is pulled into the master sheet using INDEX/MATCH.

In addition to "sheet B", there is a "sheet C" and "sheet D" that do the same thing. All info that matches the unique incident number is pulled into the master sheet using INDEX/MATCH. I have done this for a total of 100 columns, each with a unique INDEX/MATCH formula, and each one of those formulas uses a cross-sheet reference. I still have 6 columns of data to pull into the master sheet.

I need to have all corresponding info in the same row. The data on the master sheet is shared via Dynamic View with multiple users to track the status of each incident number.

Is there another way to pull data into my master sheet and accomplish what I am trying to do? If I use the automated "copy row" workflow, it will only put the info at the bottom of the master sheet and not on the specific row necessary.

Shawn Church

Additionally - here is the error i'm getting:

Hollie Green

The only solution I can think of is to add some helper columns to your reference sheet and combine some columns and then split them back out on the destination sheet. That way you use fewer cross references. You can then hide the joined helper columns on each sheet.

Shawn Church

Brilliant!