Write a formula to link data from one sheet to another when a box is checked
Hi
Trying to set up a separate risk register smartsheet where a task in a project plan is linked out to the risk register when a box is check (to capture the task as a risk that needs to be mitigated).
Open to suggestions of how to do this.
Cheers
Answers
-
in the new smart sheet
=IF( (Count{link to check box column})=1, {name of project},)
both ranges are references from source sheet
-
Hi @Wade in NZ
What about creating a Report?
You can filter by if the box is checked. This way you can make updates to the Report and it will automatically update the underlying sheet.
See: Build a row report and Create Filter Criteria to Control Data in Report Builder
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Where is this box you are checking? Are you wanting to automate the box being checked based on specific criteria? Are you able to provide some screenshots for reference (sensitive/confidential info can be blocked out)?
-
We're just implementing Smartsheet across our NPD. I'm using the PMO templates as a basis for these projects. Currently we run a separate risk register in Excell but want to show the task/mitigation work in the main project Smartsheet. So rather than have the risk details manually entered separately into the plan and risk reg I thought I'd pull the info from the plan in to the newly created risk reg Smartsheet (and vice versa if required).
A report would do part of what we need but we have a specific set of metrics we measure risk and risk mitigation success with and I didn't want to be adding more columns to the project plan sheet.
My thinking was to add a checkbox column to the plan sheet, that is ticked to indicate a project risk that needs to have mitigation work completed and then right the equivalent of a VLOOKUP/XLOOKUP type formula to place the task details, owner, start/end and progress in the risk reg.
-
Ah. I understand now. Do you have a unique identifier for each task?
-
Basically, no. The idea of the check box was to give risk mitigation tasks a unique identified but if there is a better way of doing this, I'm all ears...
-
My suggestion...
Use an auto number type column in the risk register to assign a risk id. I would ensure there is a unique id for each task as well.
From there you can submit a new risk, enter the task id, and then use INDEX/MATCH with cross sheet references to pull the rest of the task info over to the risk register based on the task id.
You can also use a formula with cross sheet references to automatically check a box on the project plan next to the task to indicate that there is an open risk associated. You could even use a formula to pull the risk id(s) over as well.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!