Write a formula to link data from one sheet to another when a box is checked

Wade in NZ · 04/11/22

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

Intern98 · 04/11/22

in the new smart sheet

=IF( (Count{link to check box column})=1, {name of project},)

both ranges are references from source sheet

Genevieve P. · 04/13/22

Hi@Wade在新西兰

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 reportandCreate Filter Criteria to Control Data in Report Builder

Cheers,

Genevieve

Paul Newcome · 04/13/22

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)?

Wade in NZ · 04/13/22

我们只是在我们的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.

Paul Newcome · 04/14/22

Ah. I understand now. Do you have a unique identifier for each task?

Wade in NZ · 04/14/22

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...

Paul Newcome · 04/14/22

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.

Write a formula to link data from one sheet to another when a box is checked

Answers

Help Article Resources

Categories