Cell link data from helper sheet to a new sheet

kmorsesmartsheet

Hello, I have an initial sheet (Sheet A) automation then copy's (status triggered) rows to a helper sheet (Sheet B). Cells from helper sheet (Sheet B) are linked to a new sheet (Sheet C). I am now looking to automate this process where each time a row is copied into Sheet B the same linked cells from the new row copy to Sheet C. Do I need to apply the linking to a row rather than an individual cell - is that possible?

Gia Thinh

Hello @kmorsesmartsheet

In order to automate cell linking from the new row in sheet B to sheet C, you may want to use Smartsheet's DataMesh add-on. Each time a new row is added to sheet B and then copied to sheet C, DataMesh can create cell links between them based on a lookup column.

kmorsesmartsheet

Thank you Gia, I do not have authority to purchase an add on. Was looking for a way to create a formula to accomplish data copy to new sheets without an entire row. I read by using a helper sheet or row you could do this but haven't been able to work through that process.

Gia Thinh

I understood your situation. Below is a solution using formulas to synchronize data from sheet B to sheet C that you may consider for your use case.

Sheet B - with existing company data as an example. The Company ID is an auto-number column to make each row unique.

Sheet C : pull in specific data from sheet B using formulas.

• Row ID column is pre-populated with numbers, as much as you want, used as row ids.
• Company ID : column formula, pull in all company ID data from the sheet B
  =IFERROR(INDEX(COLLECT({Sheet B-Company ID}, {Sheet B-Company ID}, <>""), [Row ID]@row ), "")
• Company Name : column formula, pull in Company Name from sheet B based on the Company ID on the same row.
  =INDEX({Sheet B-Company Name}, MATCH([Company ID]@row , {Sheet B-Company ID}, 0))

Hope this will works for your use case.

kmorsesmartsheet

Thank you Gia - it appears this may be an option for me. I'll give it a go when I have a moment to try it out.

Vivien Chong

Using the same concept at @Gia Thinh , we can now utilise Table View for easier data linking?

Steps here: https://help.smartsheet.com/articles/2483494-manage-column-links

Gia Thinh

@Vivien Chong nice to hear from you.

You're right, we can ultilize column link feature in Table View for data linking using the Company ID as the lookup column for both source sheet and target sheet. This means the cell values in the Company ID must be already exist before, then we can use column link feature to map the Company Name.

However, the Company ID is a column formula based on the Row ID column. Therefore, we can not fully ultilize column link feature in this use case.

kmorsesmartsheet

If I'm reading Gia's comment regarding Row ID I believe this option may help. My original sheet that copies the data does in fact have an automated REQUESTER ID column that copies into the helper sheet B. If I understand above working in Table view (which I do whenever possible), I can utilize this ID to link columns rather than cells? I will review linked article above and research this further. Thank you.