Formula needed for duplicate form entry

Stacy Bobbitt

I am new to Smartsheet and can't find a solution to my issue. I created a request form to have Professional dues paid and I want to write a formula to have the new line entry look at the preceding entries to see if the employee's name is already there. I've tried a countifs and a join formula but neither are doing the job. Any help would be greatly appreciated!

Stacy

Paul Newcome

Happy to help! 👍️

The $ before the row number locks that row number in. That way when the formula is dragfilled or auto-populated, it maintains the reference to row one instead of updating. This can also be used to lock in a column reference if you are dragfilling across a row.

Paul Newcome

Try something like this (replace "Name Column" with the actual column name) in a checkbox type column.

=IF(COUNTIFS([Name Column]$1:[Name Column]@row, [Name Column]@row) > 1, 1)

This will check the box for all duplicates leaving the first instance unchecked.

Stacy Bobbitt

That worked thank you so much! I had everything but that darn $1. What does that do?

Paul Newcome

Happy to help! 👍️

The $ before the row number locks that row number in. That way when the formula is dragfilled or auto-populated, it maintains the reference to row one instead of updating. This can also be used to lock in a column reference if you are dragfilling across a row.

Stacy Bobbitt

Here is my formula:

=IF(COUNTIFS([Employee #]$1:[Employee #]@row, [Employee #]@row) > 1, 1)

Here is my question: Everything works well until I get to row 10, the checkbox is no longer being checked even though the same Employee ID number is being added to the sheet. If I add a row between the existing rows, and enter the same employee ID, the checkbox gets checked. Is there something wrong with this formula or what is going on with my smartsheet?

Stacy Bobbitt

@Paul Newcome do you have any ideas on this?

Paul Newcome

Start by making sure the formula is being pulled into the added rows at the bottom of the sheet. There are some specific rows for auto-fill, and it may be that your rows entered at the bottom are not meeting them which means the formula is not auto-filling into the new rows which in turn means that the box will never be checked.