Populating a checkbox based on the value from another worksheet

Dana Arrell

I have a checkbox column on one worksheet that I would like checked based on the data from another sheet - basically changed to "Approved". The first sheet is named Project Documentation Tracker and the second sheet is named Approved Selections. It would be based on Approved Selections Column Category = Project Documentation Tracker Subcategory, approved selections client approval = Approved, check checkbox.

I have tried an IF statement in the project documentation tracker referencing the other worksheet but keep getting an error. Thank you

Sheryl Roland

I use a helper column (Checkbox) on the main data sheet. Next to the Approved column, add a helper checkbox column to check if it's approved. What this formula looks at is if the Approved column is checked comparing to the Unit column to MATCH that row, then it's checking the 2nd sheet Approved column.

Source Sheet (sample data):

Unit      Status                 Approved(Checkbox)      Formula

1             Approved           Checked                             =IF(Status@row = "Approved", 1, 0)

Approved Selections Sheet:

Unit      Approved

1             =INDEX({Checkbox}, MATCH(Unit@row, {Item}, 0))

Remember you can't just copy paste since you are referencing another sheet. Let me know if that worked for you.

Dana Arrell

The above is populated by a form. When the above client approver approval gets populated with approved (there are three selections, one being approved) I would like to populate the below spreadsheet with checked. You cannot see it but in the approved selections spreadsheet there is a column called category that matches subcategory in spreadsheet below

I tried your suggestion above to add the checked column but could not get it to work. Thanks for all the help

Paul Newcome

Is there also a column in the form sheet that contains the Project?

Dana Arrell

It is part of a workspace specific to each project. Approved selections is populated more from a sheet within each workspace, not a true form. This might be my issue, Project Documentation Tracker, is what I am trying to use across each project workspace. This is the sheet that populates approved selections when a client gets an email to approve the selection, thank you

Paul Newcome

Ok. Try something like this...

=IF(INDEX({Reference Sheet Approval Column}, MATCH(Subcategory@row, {Reference Sheet Category Column}, 0))