Assigning value to the newly created duplicate rows without changing the first entry


I am working on a formula for the column that should tell me whether Vendor Name in my Vendor column is entered for the first time ("New Vendor") or whether it has been already used ("Renewal"). Logic for those values New Vendor/Renewal values is:

New vendor (Meaning first ever request raised for the vendor) if:

  1. No other requests for this vendor (OR)
  2. Other requests came in later (so when this request was raised, it was new and it will stay new)

Renewal if:

  1. At least one other request exists for this vendor that is older than this request even if they were not completed, just initiated.

I tried variety of options: =IF([System Created]@cell < (MIN(COLLECT(Vendor:Vendor, Vendor@row, [System Created]:[System Created]))), "New Vendor", "Renewal") also =IF(countifs(Vendor:Vendor,Vendor@row,[Date Created]:[Date Created],@cell>=[Date Created]@row),<1,"New Vendor", IF(COUNTIFS(Vendor:Vendor, Vendor@row, [Date Created]:[Date Created], @cell <= [Date Created]@row) > 1, "Renewal"))) but I am getting changes to the first ever entry for that Vendor or #UNPARSEABLE result. Any suggestions for the correct formula?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!