Formula to link all columns in multiple sheets
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))
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
-
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)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!