Dynamic Cross-Sheet Reference to Schedules from Master Tracking

BradM

We have a Master Tracking sheet with Project Names and we rollup data from individual schedules named (Project Name)_Schedule. In the Master Tracking sheet, I would like a cross sheet reference created to the schedule without having to manually create them.

Is there a way to do a column formula that takes the Project Name, creates the cross sheet references for the formulas to the appropriate schedule? Or, uses the internal sheet ids from smart sheet once the schedule sheet is created?

Currently, I have the formula 'hardcoded' to the correct schedule. When I add a new project, I need to create new cross sheet references to the project schedule. This is less than good.

Paul Newcome

Yes. If the sheets already exist, you can certainly do that. I was under the impression that the sheets did not already exist and you needed to link everything as they were created.

Paul Newcome

How are you creating new projects?

BradM

@Paul Newcome, Yes We add projects to the master tracker (new row). We create the schedule from a template. I then link 6 cells in the tracker to the schedule. It is a bit of a pain as I am the only one who does it. Some people add projects but never notify me to add the links.

We are still early stages of using Smartsheets with these individualized schedules. Previously, the users managed their own schedules and were responsible for the entries in the tracker.

If there is a better way than adding a new row in the track and using a template schedule, I am willing to change.

Paul Newcome

I strongly suggest looking into the premium add-on Control Center. Otherwise you are stuck with a very manual process.

BradM

I will see if we have Control Center licenses. Something else to learn :-)

BradM

@Paul Newcome or others. If we do not have Control Center, is there a solution other than creating the cross-sheet links manually? Could I do the same across 2 sheets into a report and if the schedule does not exist, simply error out with a message, "No Schedule"?

Paul Newcome

You can leverage a report if you want to build out a sort of template folder type of structure. When you save a folder as new, cell links and cross sheet references are automatically updated to feed from the new source sheet. We can leverage this by creating a second sheet that has the various cell links already made and laid out in a horizontal format.

Assuming all folders would be saved in the same workspace, you can create a row report to reference this workspace (so new projects are automatically added to the report). Since you can filter on sheet name in a report, you can set it to only pull in from this second sheet (assuming consistent naming conventions) and only show that first row where the cell links exist.

BradM

@Paul Newcome, I was out yesterday and just got back to this. I don't completely follow your line of thought since I do not have the same level of experience.

I had another thought if we could not add the complexity you outlined in your last post. Could I prepopulate the references since the schedules are available as Smartsheets already? Then, copy/paste the formulas with the references instead of creating them manually.

I found once I created the reference, I could change my formula for a different group id through copy/paste. For example:

Manually create the references for the first,

=JOIN(COLLECT({Ribeye_Schedule | TaskName}, {Ribeye_Schedule | HiddenStatus}, 1, {Ribeye_Schedule | HiddenGroup}, ="A"), ", ")

Then, copy/paste, change A to B:

=JOIN(COLLECT({Ribeye_Schedule | TaskName}, {Ribeye_Schedule | HiddenStatus}, 1, {Ribeye_Schedule | HiddenGroup}, ="B"), ", ")

Let me know if you need more context.

Paul Newcome

Yes. If the sheets already exist, you can certainly do that. I was under the impression that the sheets did not already exist and you needed to link everything as they were created.