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

Wade in NZ

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

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.

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

Paul Newcome

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

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.

Paul Newcome

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

Wade in NZ

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

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.