Look for check in reference sheet checkbox

skclark9

I am looking for solution to check a box in one sheet if a box is checked in a different sheet. I tested formula with same idea in current sheet with no issues. It just does not work with reference sheet.

=IF({Regulatory}@row = 1, 1, 0) #UNPARSEBLE

If I substitute reference with column in current sheet it works fine

=IF(IsProject@row = 1, 1, 0) Good

The final desire is to check a box in one sheet if a box is checked in any one of three boxes in a different sheet. But I couldn't get it to work for one box so I thought I would start with less complexity.

AravindGP

Hi @skclark9

When you're using cross sheet references, @row function will not be used. I assume your reference is looking at an entire column and not just a cell. If it is just one cell, your formula would be =IF({Regulatory}=1, 1, 0). If you're referencing the entire column in the source sheet, you might need to narrow it down to a row to look for the value. Your formula would probably be =IF(INDEX({Regulatory}, MATCH([Search value column in destination]@row, {Column reference of the same search value that is present in the source}, 0))=1, 1, 0)

skclark9

Hi @AravindGP Your solution did work for 1 variable. Prior to your answer I did find an answer on my own. I was able to create an answer with

=INDEX({Regulatory}, MATCH([Project Name]@row, {Project Name}, 0) which did the same thing as your final IF(Index/Match) statement

However my real problem is at the bottom of my post. I need to Index 4 columns to see if any of the 4 has a box checked (I originally stated 3, but it is now 4). Would a nested IF statement work and if so what would it look like?

Paul Newcome

I would suggest inserting a helper column (that can be hidden after setup) on the source sheet that will check a box if any one of the four are checked.

=IF(OR([Column 1]@row = 1, [Column 2 ]@row = 1, [Column 3]@row = 1, [Column 4]@row = 1), 1)

Then in the target sheet, you would use the INDEX/MATCH but pull in from this helper column.