IF statement to pull value from another sheet if a checkbox is ticked

Kyle Furner

Hi all,

 

I'm trying to have a link between a risk register into an issues register and I don't know if it's simply my code or if this isn't possible in smart sheets.

I have a column in my risk register with a checkbox that gets ticked when a risk has become an issue. 

I want my issue register to have a column listing the associated risk ID.

I want the cells in this column to essentially look at each risk row and pull the Risk ID into the issue register only if the box has been ticked. 

I have a single column range for the Risk ID column and another single column range for the check box column. My code is below but I keep getting errors.

=IF({Risk checkbox range}2 = 1, {Risk ID range}2,0) 

The formula above returns the #UNPARSEABLE error

=IF({Risk checkbox range} = 1, {Risk ID range},0) 

Removing the row number returns #INVALID OPERATION

If I just link to the checkbox cell, it returns "true" if ticked and nothing if unticked.

If I replace the '1' in the formulas above with "true" it makes no difference to the errors I receive.

 

Any help would be greatly appreciated. 

 

Paul Newcome

Are you able to provide some screenshots with sensitive/confidential data hidden, removed or replaced with "dummy" data?

 

By the sound of it though, a report should work for you.

Kyle Furner

Hi Paul,

See below a screenshot of the Risk Register.

https://drive.google.com/open?id=1ux6V8IK-qdbQoXZ-VTfqi6okw8Eu3atK

Each risk has a row followed by ten rows for treatment actions. If the risk is realised, it becomes an issue and the box in the second last column (called 'Progressed to Issue') gets ticked.

 

Here is the Issue Register screenshot.

https://drive.google.com/file/d/1f_uXuyUHBpZp70DPvSEGK5uOck9BVBK2/view?usp=sharing

Once the box gets a tick in the Risk Register, I want to pull the Risk Reference number into the Issues Register. Once this value is pulled into the register it can be used to INDEX/MATCH all the other required fields. 

 

A report won't work for my scenario because of how these issues are escalated and feed back into the risk register. I'm just looking to see if it is possible to copy a value from one sheet to another IF a checkbox has been ticked.

Kyle

 

 

Paul Newcome

It is possible, but it will require a fair amount of setup and advanced planning. The basis of it is that you will need to join together all appropriate reference numbers into one long string of text. You would then pull that string to the other sheet and parse it out. You would need to plan ahead and prep the sheet for the maximum number of rows possible to avoid losing data.

 

Can you provide more details into the workflow of things?

Who initially enters the issue? How exactly is it determined that the row needs to be pulled?

Which rows need pulled (just the parent rows or the child rows as well)? If child rows need pulled, do you need to maintain that specific hierarchy?

Do you need to pull the entire row? 

What happens once it is pulled?

Will it remain on that second sheet, or will it eventually be removed and added to a different sheet?

What edits are needed once it moves to the second sheet? Is it just updating existing data, or are there new columns for new data added?