Reference Formula

ALEX BOSTROM

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

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

I would do that one of two ways.

1. Create multiple columns using the same formula and then add them together with a helper column.
2. 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)

Javed Hassan

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!

ALEX BOSTROM

This worked perfectly! Thank you Javed. If I wanted to search across multiple sheets, how would I add that into this formula?

Javed Hassan

I would do that one of two ways.

1. Create multiple columns using the same formula and then add them together with a helper column.
2. 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)

ALEX BOSTROM

Hey Javed, I just tried the formula you gave me for multiple sheets and now I am getting a Boolean Expected error message. Do you know why this would display?