I am currently working on implementing duplicate detection logic in our Baseline sheets within Smartsheet.
The requirement is to identify duplicate Project Codes and assign a sequential baseline order (1st, 2nd, 3rd, etc.) based on the order in which rows are added.
While attempting to implement the logic using a COUNTIFS formula, I encountered an issue when referencing the Auto Number system column (rowid) as part of the range criteria. Smartsheet is not recognizing the Auto Number column in the COUNTIFS range comparison (e.g., [rowid]:[rowid], <=[rowid]@row@row ), resulting in a formula error.
It appears that Smartsheet does not support using Auto Number system columns directly within range-based conditional comparisons in COUNTIFS formulas.
Could you please confirm the recommended approach for maintaining row-order-based duplicate sequencing across multiple sheets?
Possible alternatives I am considering:
- Using the Created (Date) system column for sequential comparison
- Introducing a helper numeric column to simulate row sequencing
Please advise on the most appropriate and scalable solution, as this needs to be implemented on multiple sheet
Formula =
=COUNTIFS([.Project Code]:[.Project Code], [.Project Code]@row , rowid:rowid, <=rowid@row )
