INDEX/MATCH Function to check boxes on two separate sheets

jma362

Hi!

I'm trying to use the INDEX/MATCH function to automatically check a checkbox on one sheet once it's been checked on another sheet. Specifically, I have one sheet tracking donor funding, and a second sheet for donor projects. When funding for a specific project is complete I would like to check a box in a "Ready for Construction Phase" column of the funding sheet, and have that box automatically checked on the project sheet.

This is what I've come up with so far:

=IFERROR(=INDEX({Landscape Projects Range 1}, MATCH(Project@row, {Landscape Projects Range 2}, 0)), "")

With this formula I don't get any errors, but the checkboxes on the donor sheet become uncheckable.

Please help!

Jeff Reisman

@jma362 When there is a formula in a field, you can no longer use that field manually. But, using two helper columns, there's a workaround. (Helper columns can be hidden so that they stay out of the way.)

Create your first helper checkbox column "ReadyForCons1" on the donor project sheet, and use the formula you have above in the helper column. It should become checked when the criteria in the formula are met. Create your next helper checkbox column, "ReadyForCons2", and use the formula =IF([ReadyForCons1]@row = 1, 1, 0)

Next, set up a Change Cell Value automation. Set it so that the trigger is when ReadyForCons2 changes to Checked, and action is check the Ready for Construction Phase checkbox.

(To prevent infinite approval loops, cells containing cross-sheet formulas or cell links will not trigger automation which automatically changes the sheet - Move Row, Copy Row, Lock Row, Unlock Row, Approval Request, Record a Date, Assign People, Change Cell, Clear Cell. To get around this, we're using a cell link to change a cell in ReadyForCons1, then using a formula in ReadyForCons2 to change its value, thereby triggering the automation. If this still doesn't work, you'll have to set the automation to run scheduled instead, like every hour or so, with a condition of ReadyForCons2 being checked.)