How to reference another sheet in a formula

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.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!