How to prevent Countifs column formula from updating previous cells.


I have created a log book request form that needs to generate a unique ID (YYYY-##-###) based on 3 criteria: Year submitted (4 digit year), Site Code (preassigned 2 digit site code), and Log Book Type request count. The formula I'm using performs as needed, except it changes previous Log book numbers that have rows with matching information. In the screenshot below, Row 1 originally had New Log Book # 2023-01-02, but when a new request on row 3 came in, the New Log Book # was updated in both rows. Is there a way to prevent this from happening with the use of absolute reference or helper columns?

=YEAR([Date Requested]@row) + "-" + LEFT([Site/Location]@row, 2) + "-" + COUNTIFS([Log Book Type]:[Log Book Type], [Log Book Type]@row, [Site/Location]:[Site/Location], [Site/Location]@row, [Date Requested]:[Date Requested], [Date Requested]@row)


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!