Link cell automation

ejrbean

This is probably a "no," but I am working on transferring a lot of Smartsheet data from one sheet to a new format, and it is requiring a lot of manual labor, so I'm trying to automate as much as I can. One thing I am doing requires me to link the cells in certain columns to another sheet. Is it possible to set up an automated workflow to automatically link the "IRB app..." "Study Status," and "Image Type" in the first sheet to the corresponding cells in the second sheet, if the Protocol IDs match?

If there are any other suggestions on how to do this more quickly, I'm open to alternative approaches! I should note that I need these to be linked because I need both to update when one is changed.

Thank you!

Andrée Starå

Hi @ejrbean

I hope you're well and safe!

Cross-sheet formulas are connected, so it will always show the correct information.

More information with examples.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Did that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

What column or columns is this range referencing?

{PRMC 2021 ID IRB Status}

Paul Newcome

There is not a workflow, but you can use INDEX/MATCH or VLOOKUP

ejrbean

Is there a way to use those formulas to link cells? I thought those would only transfer the current values but not create a dynamic link. Can you explain how I would use those (maybe with an example)?

Andrée Starå

Hi @ejrbean

I hope you're well and safe!

Cross-sheet formulas are connected, so it will always show the correct information.

More information with examples.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Did that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ejrbean

I'm trying the index match but am having difficulty. I am attempting to write a formula to search another sheet for a Protocol ID, then get the "current status" for that protocol ID (same row, different column), and have that current status show up in the sheet with the formula. This is the formula I wrote (which it says is unparseable):

=INDEX({PRMC 2021 ID IRB Status}, MATCH([Protocol ID]@row, {PRMC 2021 Protocol ID},0),3))

So for example, I have the protocol ID EA2174. I would like to write the formula such that it would search the other sheet (second screenshot) for the protocol ID for that row (e.g., EA2174), then return the current status (Open Active, for EA2174).

Can someone identify where my formula is incorrect? Would VLOOKUP be a better function to use here?

Paul Newcome

What column or columns is this range referencing?

{PRMC 2021 ID IRB Status}

ejrbean

@Paul Newcome Sorry that was unclear!

{PRMC 2021 ID IRB Status} is a reference to the three columns in the second image above - Protocol ID, IRB-Approved, and Current Status.

ejrbean

@Paul Newcome & @Andrée Starå - After a bit of trial and error figuring out what everything means in the INDEX MATCH formulas, I managed to get it to work! Thanks so much for your suggestions!!

Andrée Starå

@ejrbean

Excellent!

Happy to help!

Paul Newcome

Happy to help and glad you were able to get it working. 👍️