Reference Formula
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?
Best Answers

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!

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)
Answers

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!

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

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)

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?
Help Article Resources
Categories
Check out the Formula Handbook template!