Changing / Moving Existing Profile Data - Intake Sheet to Project Sheet

ro.fei

Hi!! We have a large program & want to change where some of our Profile Data fields are being entered without breaking things / renaming fields / doing a ton of avoidable repairs. I'd love some advice on common issues / the best approach!

-------

Program Setup (for reference if needed, feel free to skip to the goal 😊)

Our program is ~4 years old with 2 blueprints feeding from 1 Intake Sheet; the blueprints are almost identical with minor differences to various tasks/lines. Both have the same assets (3 dashboards, 10 reports, & >20 sheets), & all PD fields are identical & link to the same places down to the cell. Intake Sheet PD fields link out to project Metadata sheets, & PD fields originating in project sheets do the same (i.e., every Metadata sheet holds all PD for its project).

From the Metadata sheets the fields link to our Summary Sheet, which contains all PD for every project regardless of origin. ~Half our PD fields link from the Intake Sheet (Intake > Metadata > Summary) & the rest link from projects sheets (Project Sheets > Metadata > Summary). All PD fields are set to Cell Link behavior; we do not use One Time Write or Editable Value behavior for any PD fields as we want them to be the same everywhere--the Intake, Metadata, Summary, & project sheets should all display the same value for each field.

-------

Goal: Move existing Profile Data fields so originate in project sheets instead of the Intake Sheet--& remove the Intake Sheet columns with as little damage as possible.

For context, we have a lot of active projects (almost 500), a lot of PD fields (~135 plus >2 dozen being added soon) & a lot of users (~150 & growing) with varying Smartsheet experience. Because of this (& things being accidentally broken in the past), most users have restricted access--especially to the Intake, Metadata, & Summary Sheets. A lot of PD is entered/updated in the Intake Sheet after provisioning (I know I know, I didn't build it). This worked when it was just a small group of users very familiar with the program, but as it's grown & access has been limited, it's been putting a strain on those that still have editing/admin access & fields aren't being updated as often/accurately as a result.

Before it's suggested, we have tried building Dynamic Views & the like; it's worked for some cases, but for many it didn't make much sense & adoption has been dismal. We're done building things that go unused & forgotten, & frankly with so many projects, updating project sheets is much easier (& more logical for these fields) than wading through massive reports. These fields simply don't belong in the Intake Sheet & we'd like to move them where they logically belong & the right people can access/update them easily. (To give some examples, some of these are date fields that should be populated in the Project Plan, or financial fields that belong with the rest in the Finance sheet, etc.)

Our main goal is to do this without losing data/destroying connections to the Summary Sheet; many of these fields have data for >half our projects that needs to continue to be updated, & damage would likely mean manually fixing hundreds of sheets and/or links. We want to remove the Intake Sheet columns for these fields entirely (we want them gone not hidden) & replace the Intake > Metadata cell links with Project Sheet > Metadata links from the relevant project sheets (where they'll be updated going forward) so the data can continue to flow to the Summary Sheet.

My question (/hope) is if anyone knows the best way to do this that will avoid lost data & minimize the amount of manual edits/repairs needed? I've changed the names of PD fields & moved fields from one project sheet to another, but going from Intake Sheet to Project Sheet is a new one for me & I'm wary of breaking things/spending the rest of my life updating cell links by hand. I'm not sure how to start, what needs to happen to the Intake Sheet columns, how the blueprints need to be updated, what order to do it all in...

If anyone has any tips, tricks, warnings, charms, spells...anything that could help us use Control Center, Global Updates, etc. to make this process faster/easier I would really appreciate the help! Luckily I do have a field that we'll be removing entirely, so I'm hoping I can use that as a guinea pig field to give it a test run. At this point I'm even willing to add a dummy field just to test out moving it without breaking things. I could really need some guidance if anyone has any to offer!

-------

Thank you so much in advance!! Feeling very stumped on this & I'm so glad I have this community to turn to for some help 🌻

Gia Thinh

I understand your situation as below:

Change the cell-link : FROM Intake-Metadata-Summary TO Project-Metadata-Summary

The key point is that the cell-links between the Intake sheet and Metadata sheets are existed in 250 projects (estimated) and now you want to change to making cell-links between Project sheets and Metadata sheets so that you can remove some columns in the Intake sheet.

I got an idea with steps for your reference:

For new projects to be created in the future:

1- In your current Blueprint folder, you can create cell-links between the Project sheet and Metadata sheet for the profile data you want.

2- In the Intake sheet : Rename the columns you want to remove in the future so that they should not be matched with the profile data of the Project Metadata sheet in the Blueprint. This will stop Control Center to make cell-links between the Intake sheet and the Project Metadata sheet when a new project is created.

3- In Control Center, you can modify the Blueprint setting at the step of "Profile Data Selection" to make sure not including profile data from the Intake sheet to the Project Metadata sheet.

The outcome for 3 steps above is : new projects will be created with cell-links between the Project sheet and Metadata sheet as you want.

For current projects:

1- It is necessary to manually change the cell-link FROM Intake-Metadata TO Project-Metadata (because I don't find any tools in the Global Update of Control Center to make a mass update automatically for cell-links). These changing can be done manually by :

• a group of people like you in a short time, or
• project members when they want to update fields (will take a longer time)

2- Once completing all the cell-links changing, you can remove the renamed columns in the Intake sheet.

Hopefully my above idea is usefull for you to deal with this big changing. You may also want to seek the help from Smartsheet experts for a better consulting.