Cross sheet reference strategy
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.
Help Article Resources
Check out the Formula Handbook template!