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

Options

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

Answers

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • TolerDo
    TolerDo ✭✭✭✭
    Options

    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.
    Options

    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?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • TolerDo
    TolerDo ✭✭✭✭
    Options

    HI Genevieve,

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

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!