Data shuttle - how to update with recurring reference column

22Wordsmith

I have a 1000 or so lines in my sheet. I need to update a set of contacts so I've set up a data upload but it only changes the data for the first instance of my reference column.

Example
Family Members
Flintstones Fred, Wilma, Pebbles, Barney, Betty, Bam Bam (my favourite)

Muppets Kermit, Ralf, Animal, Beaker, Swedish Chef, Fozzie

I want to upload an update that finds every instance of Family and inserts the latest list of Members. At present, using FAMILY as the unique reference means it only looks for one instance.

Suggestions on how to do comprehensive update would be appreciated.

22Wordsmith

Thanks for confirmation. V messy. I will step back to a simple filter and fill-down. Easier to explain to others and fix when it goes wrong.

swfascetti

You might find data mesh helpful here. Data shuttle brings data to a sheet. You might want a separate look up table that links each faculty member to say the same last name that you then mesh into the main sheet.

22Wordsmith

Thanks - we don't use data mesh. The contacts (Members in my example) are already a compiled list from another sheet of single-line entries. It's the only way I found to get the grouped information into Smartsheet so that they were recognised as contacts, which is essential for our Dynamic View filters.

Darla Brown

Hi,

Since you don't use Data Mesh, have you tried using a separate sheet as your main contact sheet and then using cross-reference column formulas for your reference columns?

22Wordsmith

@Darla Brown - I did try the formula approach, thanks. Unfortunately, that means the column doesn't recognise the imported information as contacts, which is the core requirement of the process.

kowal

hi @22Wordsmith,

If you cant use DataMesh but you want to have like DataMesh functionality why not going into TableView and use the Link Column functionality there? It will fill your data automatically.

Darla Brown

^ What Tomasz said! I keep forgetting about column links. That would definitely work!

22Wordsmith

@kowal I haven't used that function as yet. Will it providing the repetition that I need to fill in 1000 lines from a set of 13 entries?

I've watched a YT video on column links but it requires a unique reference.

My data has a Reference for every line. Each line has one WORK UNIT and the work unit has a unique set of contacts that I use to filter content in Dynamic View.

The source sheet is a compilation of email addresses as contacts against the corresponding work unit.

If I understood the video correctly, I would have to (at best) re-enter the work unit in my base / target sheet to trigger the source sheet fill with column links.

These updates will need to be run at least once a quarter, sometimes more often.

The only way I can think to make this work might be to :

1 add a helper column to my target sheet to serve as a unique reference for the cell link

2 build the cell link referring to the helper column

3 populate the helper column with a formula that brings in the original work unit designation and

4 run the cell link update

To rinse and repeat (as it were), I'd clear the helper column and start over with steps 3-4.

Your thoughts/guidance would be appreciated.

kowal

hi @22Wordsmith,

Yes the unique identifier is needed whether u use DataMesh, Column Link, Index/Match or vlookup.

if you add the helping column with unique identifier it shall do the trick.

22Wordsmith

Thanks for confirmation. V messy. I will step back to a simple filter and fill-down. Easier to explain to others and fix when it goes wrong.