Reference Formula

Question

Hello SmartSheet Community,

I need a formula that can detect if a name has appeared on other sheets I've used in the past. I have a column named "Duplicate" that I would like to have automatically checked if someone enters a name under the column "Name (Last, First) that has already been entered on sheets columns labeled "Name (Last, First)" that we've used in the past.

Is there a formula that can do this?

Javed Hassan · Accepted Answer

Hello,

Try =IF(COUNTIF({Sample Range 1}, [Name Field]@row) > 0, 1, 0)

You'll need to update your reference sheet and column name accordingly.

Hope this helps!

Javed Hassan · Accepted Answer

I would do that one of two ways.

* Create multiple columns using the same formula and then add them together with a helper column.
* Within this formula after the first IF, you'd add more of the same IF formulas. See below.

=IF(COUNTIF({Sample Range 1}, [Name Field]@row) > 0, 1, 0) + IF(COUNTIF({Sample Range 2}, [Name Field]@row) > 0, 1, 0) + IF(COUNTIF({Sample Range 3}, [Name Field]@row) > 0, 1, 0)