How to prevent Countifs column formula from updating previous cells.

Annie Prescott

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)

Paul Newcome

Insert an auto-number type column with no special formatting and then add a range/criteria set to your COUNTIFS of the auto-number column being less than or equal to the auto-number column "@row".

Paul Newcome

Insert an auto-number type column with no special formatting and then add a range/criteria set to your COUNTIFS of the auto-number column being less than or equal to the auto-number column "@row".

Annie Prescott

https://community.smartsheet.com/discussion/comment/403334#Comment_403334

That was entirely less complicated than I was expecting lol. Thanks Paul!!

Paul Newcome

Happy to help. 👍️