Set checkbox color based on the color from another sheet

Jennifer Lindquist

Source Sheet: JOB TRACKING

Reference Sheet: JOB NOTES

{JOB NOTES Range 1} is the SALES ORDER column on reference sheet

Unique identifier: SALES ORDER

I'd like to check the Shipped box in the JOB TRACKING sheet by line if the SALES ORDER color in the JOB NOTES sheet is green.

I'm stuck here:

=IF(INDEX({JOB NOTES Range 1}, MATCH([SALES ORDER]@row, {JOB NOTES Range 1}, 0)) = "Green",1,0)

The formula doesn't error, but doesn't work either. All my cells are 0.

Paul Newcome

You won't be able to evaluate for that using a formula directly. Are you using conditional formatting to highlight the cells green?

If so, you can use a helper column with a formula in it to flag rows that meet the criteria and then evaluate this flagged column in your cross sheet formula.

Paul Newcome

What exactly do you mean by "Green"? Are you using the RYG symbols, or is it cell fill?

Jennifer Lindquist

The cells in the reference sheet are colored green.

Paul Newcome

You won't be able to evaluate for that using a formula directly. Are you using conditional formatting to highlight the cells green?

If so, you can use a helper column with a formula in it to flag rows that meet the criteria and then evaluate this flagged column in your cross sheet formula.