Is there a formula in smartsheet that would create a 2 way link between cells?

John Sisounthone

Hello,

I have multiple sheets that I would like to link all into one Master Sheet. I want the cells to have a two linking methods between each other. If a cell changes in the Master Sheet, I want it to reflect to the other sheets and if the other sheet decides to change the cells, it would reflect back to the Master Sheet. The current linking method that is in Smartsheet is only a One-Way link and only allows changes from one sheet. I have noticed that there are some functions missing in Smartsheet that other programs have at our disposal.

Thank you in advanced.

Kind Regards,

Johnathon S.

Paul Newcome

Honestly it sounds like you are trying to accomplish this backwards. My suggestion would be to create the Master and then use Reports for the other sheets. This will provide the 2way link you are looking for.

You could create a report that pulls from all sheets, but you would need to remember to add any new sheets to the report or they could be missed (unless they are all in a workspace and you have the report pulling from everything in that workspace instead of individual sheets).

John Sisounthone

Thank you for responding. I have tried using the reporting method but it would separate the Children rows from the Parents rows. Is there a way to have the parent and child function active in the reports?

Paul Newcome

Reports don't show hierarchy, but you can use a helper column and conditional formatting to highlight each level a different color so that it is visible on the report. When you say it separated the children rows from the parent rows... Were you accessing multiple sheets? If so, did you sort the report?

John Sisounthone

@Paul Newcome

I am currently using the conditional formatting method to separate the rows among each other. I'm sorry for not clarifying, the hierarchy is not available in the Report. When I use two separate sheets in one report, it would duplicate the rows instead of recognizing that it's the same data. Is there a reason why it would duplicate the data instead of recognizing that it is the same and just report it only one time?

Paul Newcome

Because while the data may be the same, it is coming from a different source.

If possible, I would suggest restructuring so that your main sheet is the master sheet and then the sub-sheets are actually reports instead of sheets.

John Sisounthone

@Paul Newcome

I thought about using the Report sheet as the actual sub-sheets but since it doesn't have the hierarchy function, it wouldn't be ideal. I was hoping that there was a VBA function in Smartsheet like in excel. Thank you for helping me. I hope that there will be a two way cell linking function added in the future.

Paul Newcome

You can maintain the conditional formatting on the master and the different formats will pull through to the reports.