Dynamic Link Formulas & Manual Entry Columns Misalign Upon Insert/Delete of Row on Linked Sheet

TolerDo

Hello,

I am currently working on a project in which we have a large master sheet. This has served us to this point, but now the master sheet has grown so big and contains sensitive data that we would like to store elsewhere so that there is no over-exposure of data, but at the same time, leverage the data that already exists in the master. (because who wants to enter and maintain data in 2 places?!? 😁)

We tried dynamically linking and pulling columns from the master, using a unique index identifier formula. We added columns to the sheet to maintain the sensitive information. It almost worked.

The problem we saw was if anyone inserted a row on the Master sheet, the manually entered data on the linked sheet became misaligned by row. In other words, the data manually entered in a cell on a specific row, moved up or down a row, depending on where the insert happened on the master.

Is there a way to improve upon where we started to ensure that when someone adds or deletes rows from the master sheet, that integrity between linked data and manually entered data is maintained on the linked sheet?

Thanks,

Donna

Genevieve P.

Hey @TolerDo

Yes! Instead of using an Auto-Number column as your Row Number (which will change as rows are added/deleted), you can add a second helper column that uses MATCH to show the actual row number, like so:

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

The other option would be to include a MATCH function within your current INDEX, and make sure you're matching rows by a unique value (such as a unique Project Name or ID).

=INDEX({IntName}, MATCH([Unqiue Value]@row, {Unique Value Column}, 0))

See: Formula combinations for cross sheet references

Cheers,

Genevieve

TolerDo

Hi Genevieve,

We tried applying your suggestion (INDEX and MATCH), however, when we went into the source sheet, and moved a row to test, the manually entered data on the linked sheet did not stay in sync with the rows that were moved on the source sheet.

We started playing with Data Shuttle, but that presents additional challenges to work through.

If you have any more creative ideas, would love to hear them!

Thanks!

Genevieve P.

Hi @TolerDo

In your Index(Match formula, were you then referencing the new, helper column (titled "Row Number" in my image) or the original row ID column?

TolerDo

HI Genevieve,

I believe we referenced the new "row number" column -- we were referencing your picture above when we tested.

Genevieve P.

Hi @TolerDo

Would you be able to post a screen capture like what you have in your first post, but showing the additional column and adjustment to your formula?

All the helper column is doing is ensuring that you have numbers going down your sheet in sequential order, even when data is deleted or moved (e.g the third row in your sheet will always have a 3, even if it was moved up from the bottom).

Then we're using that as the row number portion of the Index function, to identify what row to bring back:

=IFERROR(INDEX({data to return}, row number), "")

This means your second sheet should always show the same data row order as in your first sheet, as it's comparing the current row number with the row number in your master list.

However perhaps I misunderstood what you're looking to achieve. Do you have a unique identifier that's manually typed in on the rows in your second sheet, so as your formulas rearrange based on the order of rows in your source sheet it's messing up the alignment?

In this case, instead of using a row number as the matching value, you'll want to use the MATCH function so that you can compare your manually entered content with what's in the source sheet (regardless of row order and bring that back.

This article has a good example of an INDEX(MATCH combination: Formula Combinations for Cross Sheet References

Cheers,

Genevieve