Formula to link all columns in multiple sheets

sahilhq

Hey,

I have a master Smartsheet actions tracker set up with actions assigned to roughly 5 people across a project and each of the 5 personnel have their own action tracker linked to the master sheet. How it works is that any time a new task is added and the "Assigned To" value changes to an actionees name, through Smartsheet auto row copy automation, the action is copied to their respective action tracker. I would like to now link the indivudal action trackers to the master action tracker such that anytime a change is made in the indivudal action tracker, the master tracker automatically gets updated through formulas. I gave it a shot myself and used the following INDEX/MATCH formula but soon after inputting it into one of the columns in my master sheet, I realised that this way I will only be able to link 1 out of the 5 child sheets. Any suggestion please on how to link the remaining 4 sheets as well? (P.S. I realise that I can use Smartsheet reports to do what I am doing here with sheets but due to reporting limitations - mainly on how I cannot show parent / child hierarchy when building reports, I want to test the capability of sheets to manage actions).

=INDEX({Notes / Closeout Evidence}, MATCH([Unique ID (PM)]@row, {Unique ID (PM)}, 0))

Andrée Starå

Hi Sahil,

You’ll need to link each sheet to it’s own so-called helper column, but you could show everything in one column for all the sheets with the JOIN function.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

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!

sahilhq

@Andrée Starå Many thanks for the response. Could you please explain in a bit more detail how this could be done for my set up? If you could provide the JOIN formula I need to use that would be really helpful. Also, I’m struggling to picture how we can show everything under one column when we have many columns in the sheets. Can you please demonstrate this as well? Thanks, once again.

Andrée Starå

@sahilhq

Happy to help!

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)