Linking a Column to a masterfile with automatic updates

Lee (Testing) Ziervogel

Hi Community

I am creating a solution where I have linked a column on a sheet(Sheet 1) to a column in a 'masterdata' sheet using 'Link from Cell' function in Sheet 1 to the masterdata sheet.

I want to be able to update the masterdata column ......and sheet1 to be automatically updated for any changes in the linked column.... this is not happening. Please help.

Thanks,

Lee

Andrée Starå

Hi @Lee (Testing) Ziervogel

I hope you're well and safe!

Not sure I follow!

Are you adding new rows to the Masterdata sheet?

If yes, you can't use cell-linking. You'd need to use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Make sense?

I hope that helps!

Be safe and have a fantastic weekend!

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!

Lee (Testing) Ziervogel

Thanks Andree

The data from the Mastersheet is used "as the source" to vlookup against the masterdata and return information from upload sheets and the Master. Essentially I want the sheet(1) column to 'link to the masterdata as the master reference.... I hope that makes sense?

Please can you direct me to some resources and INDEX/MATCH .

Thank you!

Genevieve P.

Hi @Lee (Testing) Ziervogel

An INDEX(MATCH sounds like it would be ideal for this. The Masterdata column would be what you use in the MATCH section of the formula, and the column to INDEX will be the one you want linked.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

So in your instance, something like this:

=INDEX({Mastersheet Column to link}, MATCH([Masterdata Column]@row, {Masterdata Column in Mastersheet}, 0))

Here are some resources that may help:

INDEX Function / MATCH Function / Cross Sheet References

If you still need help building out your formula, it would be easiest if we could see some screen captures of your sheets, both the source sheet and the destination sheet in Grid View (but please block out any sensitive data).

Cheers!

Genevieve

Lee (Testing) Ziervogel

Thank you Genevieve .... here is a screen shot.. I hope it makes sense.

Genevieve P.

Hi @Lee (Testing) Ziervogel

My apologies, I misunderstood what you were looking to do. An INDEX(MATCH works the same as a VLOOKUP, so it would be similar to what you have in the other columns.

However either a VLOOKUP or an INDEX(MATCH require a unique value to match across both sheets.

It sounds like you want to automatically include the unique value from the Master Record into the Scoreboard so that the rest of your formulas can auto-populate, is that correct?

If so, @Andrée Starå 's first comment is correct. A cell-link will copy over that one specific cell, but it won't update the rows below. You'd need to either manually copy/paste the data across as new unique values are added, or potentially set up a Copy Row workflow.

Keep in mind that your column type will need to be the same across both sheets. It looks like the Unique ID in these sheets are two different types of columns. This may also be why some of your Vlookups are returning a "no match" right now as well.

As an alternative, could you create a Report instead of a second sheet with formulas to house this data?

Lee (Testing) Ziervogel

Thank you Genevieve, appreciate your lens.

The scoreboards calculate based on vlookups ... at the moment the data #NoMatch is part of the testing :)

Thanks a mil