Cross sheet reference strategy

Jason Murphy

Hi Folks,

We have two sheets, which are used by two stakeholder groups. I've populated sheet B with some of the data from sheet A (the main sheet). To do this, there is a unique and corresponding ref on both sheets, and this has been used for a vlookup formula. We've now hit the max cross references and I've converted the vlookup formula on sheet B with an Index/Match formula.

This has been working well, but with vlookup, we could pre-populate the blank rows on sheet B with the vlookup formula, then once data hit sheet A, it would auto-populate. The only thing we needed to ensure was the constant corresponding unique reference ID on sheet B.

Now that I've converted the vlookup to index/match, this no longer works. When attempting to copy the index/match formula to a blank row on sheet B, it will clone the data on the last row with data, rather than the empty formula.

A couple of questions on this:

1) Is there anything we can do to restore the earlier functionality we had?

2) Is there any way we can find out what our number of cross sheet references is?

Appreciate any help.

Thanks, Jason.

Andrée Starå

Hi @Jason Murphy

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jason Murphy

Hi Andrée,

Thanks very much for your note. Unfortunately, due to confidentiality, I can't share the sheet. I hope this generalised example might help explain things:

Sheet A

Column 1 (Ref) / Column 2 (Name) / Column 3 (Email)

Row 1 Data

Row 2 Data

Row 3 blank

Sheet B

Column 1 (Ref) / Column 2 (Name)

Row 1 Formula-Data

Row 2 Formula-Data

Row 3 Formula

To extract 'Name' from Sheet A to Sheet B, we were using vlookup. We then exceeded the 100,000 cross sheet reference threshold.

I converted the vlookup formulas to Index/Match. This has delayed our threshold for the time being.

In the example above, when using vlookup, Sheet B - Row 3 would contain the blank cell with the vlookup formula. Once data was entered on Sheet A - Row 3, the data would populate on Sheet B.

Now, after converting to Index/Match, this no longer applies.

In the scenario above, when Sheet B - Row 3 has no corresponding data from Sheet A, it simply copies the data Sheet B - Row 2.

Do you know if there is any way to replicate the scenario we had when we were using vlookup?

The other part of my question was, is there any way we can determine the number of cross-sheet references so that we can avoid any major issues going forward?

Many thanks,

Jason.

Andrée Starå

@Jason Murphy

I hope you're well and safe!

Excuse the late response.

Crazy times and I missed coming back to you.

Have you solved it, or do you still need help?

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD