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

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.

Find more posts tagged with

Sheets

Reports

Cell linking

Comments

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

risk register.png

issues register.png

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…