How can I add an individual cell link within a column with a formula applied?

Christina Bayma

I'm working with a PMO Template which includes an Intake Form/Sheet. In the Intake Sheet I have some existing rows corresponding to several existing project folders, hence individual Project Plans. We have a column for Start Date which was originally using a Cell Link to populate the Start Date value from the corresponding Project's Metadata. But I had to create the cell link every time a new project was created. So, I've created a column formula using a reference to populate the Start Date from the Project Metadata on any new rows moving forward. The column formula's reference points to the Project Metadata sheet within the Project Template folder so when the Project Template is used to create a new project the references will automatically update with the row's corresponding Project Metadata sheet. Here's the problem. My existing rows are no longer cell linked to their corresponding Projects because I've applied the formula to the entire column. I need to overwrite the Start Date for each existing row to cell link from the corresponding Project Metadata without breaking the column formula.

jmyzk_cloudsmart_jp

Hi @Christina Bayma

In such a case, I would use the Sheet Summary fields.

The Project Start Date field references a cell in another sheet, Project Metadata. To create such a formula, I use this technique.

Write a formula like =INDEX([Range},1), then remove other part except the range, to get ={Range}

Then, use the field as [Project Start Date]# in a column formula.

The demo example, which I modified the Project Management Office template, uses a checkbox column, isFirstTask, to use a column formula. Using that as the IF condition, [Project Start Date]# is the true value, and whatever formula is the false value, TODAY(), in this example, for demo purposes.

https://app.smartsheet.com/b/publish?EQBCT=b01887bfd70042d6855cb20e83ccbc5e

The {Project Metadata Range 1} reference.

Link to Smartsheet Project Office Template.

https://app.smartsheet.com/r/template-gallery/templates/0f2ab5ca-7e88-4590-85ee-06a224b1bdf4/Project-Management-Office?tg-locale=en_US