How can I add an individual cell link within a column with a formula applied?
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.
Answers
-
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.
The {Project Metadata Range 1} reference.
Link to Smartsheet Project Office Template.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!