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
-
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".
Answers
-
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".
-
That was entirely less complicated than I was expecting lol. Thanks Paul!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!