How to reference another sheet in a formula

KatieTMBA

I've been trying to write a formula that will "Check a checkbox (in sheet A) IF [name] cell (in sheet A) is found in {name} column (in sheet B), AND the box in [checkbox] column (in sheet B) is checked."

Basically, does a particular staff member's name appear on another sheet, and if so does it have a checkbox checked in that row.

I've written nested IFs, ANDs, CONTAINS, HAS, etc, statements and just can't get anything to return something other than "Invalid Operation".

Just a couple things I tried, in case I get close enough for someone to be able to fix!

=IF(AND(CONTAINS([namesheetA]7, {NameSheetB}), {CheckboxSheetB} = 1), 1)

=IF(AND({NameSheetB} = [nameSheetA]9, {CheckboxSheetB} = 1), 1)

Any help would be appreciated.

-KT

Paul Newcome

Try something like this...

=IF(COUNTIFS({NameSheetB}, [nameSheetA]@row, {CheckboxSheetB}, 1) > 0, 1)

Paul Newcome

Try something like this...

=IF(COUNTIFS({NameSheetB}, [nameSheetA]@row, {CheckboxSheetB}, 1) > 0, 1)

KatieTMBA

Thank you Paul! I just had to take the @row off and it worked just fine.

Paul Newcome

Happy to help! 👍️