Cell Links Break When Rows Moved to Another Sheet

Susan Peck

Hi,

We have a Master Schedule that tracks our projects currently in development with high-level milestones. Several departmental task sheets pull dates from the Master Schedule to assign and track smaller tasks associated with the high-level milestones. The links work great and keep everyone on schedule when dates change in the Master Schedule.

However, when a project ships to the field, it is moved from the Master Schedule used for development projects to another Master Schedule for field projects. These two sheets have the same columns, format, and so forth.

The problem is all the links that were set up for the original schedule no longer work in the departmental task schedules. Since departmental tasks continue after projects go to the field, it would be nice to not have to recreate the links again.

Smartsheet knows the links are broken, but not how to fix them by providing the updated sheet name in the links.

Does anyone have ideas for solving this problem? Some kind of formula? Could Find & Replace work to change the name of the linked sheet in the destination sheet? Anything??

Thanks for your help and ideas!

Susan

Andrée Starå

Hi Susan,

I’m thinking that we could maybe structure a solution with a cross-sheet formulas and VLOOKUP in some way. Does each row have a unique ID?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

What do you think?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Susan Peck

Hi Andrée,

Thank you for your offer to help! Give me a day or two to put some screenshots together and provide access to copies of the sheets. Since this is a common problem for us, having a solution that we can apply several places will be very helpful.

susan

Andrée Starå

Ok.

I'm always happy to help!

Susan Peck

Hi Andrée,

Apologies for being so slow. We put our collective heads together and came up with a simpler and more elegant solution that allows us to leave all project schedules in a single sheet but distinguish projects in development from projects being installed. It's so simple, I'm almost embarrassed to share it. We divided the sheet into two major sections and but the projects in the appropriate section so we can expand or collapse them.

Anyway, for now this solution works for us. In the future, we may need to do something more complex. Thank you for being willing to help. You are a great asset to this forum.

Susan

Andrée Starå

No worries! Glad that you got it working!

Thanks for the kind words!

I'm always happy to help!