Set a Checkbox Based on Color Status from Another Sheet

Geoff Hill

I'm trying to set a checkbox on sheet 1 based on the color status in sheet 2. For example, if the status on the 2nd sheet is "Blue," I want to check the box on sheet 1. Here is my formula:

=IF({Overall Completion} = "Blue", 1)

However, that generates a #INVALID OPERATION

Any suggestions?

Genevieve P.

Hi Geoff,

You would need some sort of other unique identifier... for example, a Row ID that is the same for both rows in the sheet. Then we could use a different formula, perhaps an INDEX(MATCH or a JOIN(COLLECT to return the checkbox, but it won't know which row to look at unless we have some sort of identifier.

As an alternative, what about using a cell-link for that coloured status cell, bringing in that column or cell to the current, second sheet? Then you could run a simple IF formula like what you have, except instead of a cross-sheet reference it would be looking at the cell in its own row:

IF([Overall Completion]@row = "Blue", 1)

If cell-linking won't work for your set up, it would be useful to see a screen capture of both sheets (but please block out any sensitive data) with an explanation of why the box is being checked.

Thanks!

Genevieve

Genevieve P.

Hi Geoff,

You would need some sort of other unique identifier... for example, a Row ID that is the same for both rows in the sheet. Then we could use a different formula, perhaps an INDEX(MATCH or a JOIN(COLLECT to return the checkbox, but it won't know which row to look at unless we have some sort of identifier.

As an alternative, what about using a cell-link for that coloured status cell, bringing in that column or cell to the current, second sheet? Then you could run a simple IF formula like what you have, except instead of a cross-sheet reference it would be looking at the cell in its own row:

IF([Overall Completion]@row = "Blue", 1)

If cell-linking won't work for your set up, it would be useful to see a screen capture of both sheets (but please block out any sensitive data) with an explanation of why the box is being checked.

Thanks!

Genevieve

Geoff Hill

Thanks Genevieve! The cell-link idea worked!

Genevieve P.

Hi Geoff, that's great! Thanks for letting us know. 🙂