Custom auto-number system

Jessica Howell

We would like to create a reference, auto- number system that takes the Facility ID entered, and the date of incident, to look something like this. (####- 05/15/2020)

Does anyone know if its possible to do this?

Thanks in Advance!

Paul Newcome

It is definitely possible. Do you currently have those two columns created? If so, try something like this...

=[Facility ID]@row + "- " + [Date Column]@row

Paul Newcome

It looks like there is a space between your first COUNTIFS and the following open parenthesis. Try a copy/paste of this one. I have updated the column names to match what you have in your above screenshot. I also recommend leaving the @row reference in. It basically replaces the row number with whatever row the formula happens to be on. It is a little more efficient on the back-end and helps avoid typos.

=[Facility ID]@row + "- " + [Date Column]@row + IF(COUNTIFS([Facility ID]$1:[Facility ID]@row, [Facility ID]@row, [Date of Incident]$1:[Date of Incident]@row, [Date of Incident]@row) > 1, "-" + COUNTIFS([Facility ID]$1:[Facility ID]@row, [Facility ID]@row, [Date of Incident]$1:[Date of Incident]@row, [Date of Incident]@row))

Paul Newcome

It is definitely possible. Do you currently have those two columns created? If so, try something like this...

=[Facility ID]@row + "- " + [Date Column]@row

Jessica Howell

@Paul Newcome That worked! Say there is more than 1 incident in the same day.. would you happen to know a function to add -2, -3...and so on, to the end ?

Paul Newcome

Try something like this...

=[Facility ID]@row + "- " + [Date Column]@row + IF(COUNTIFS([Facility ID]$1:[Facility ID]@row, [Facility ID]@row, [Date Column]$1:[Date Column]@row, [Date Column]@row) > 1, "-" + COUNTIFS([Facility ID]$1:[Facility ID]@row, [Facility ID]@row, [Date Column]$1:[Date Column]@row, [Date Column]@row))

Jessica Howell

@Paul Newcome Hi Paul, do i use the same column 2 column fields to fill the rest of the bolded area?

Paul Newcome

You should only be referencing two columns throughout the formula. The [Facility ID] and the [Date Column].

Jessica Howell

Coming up unparseable... do you see any issues? or any other tips?

Jessica Howell

@Paul Newcome forgot to tag

Paul Newcome

It looks like there is a space between your first COUNTIFS and the following open parenthesis. Try a copy/paste of this one. I have updated the column names to match what you have in your above screenshot. I also recommend leaving the @row reference in. It basically replaces the row number with whatever row the formula happens to be on. It is a little more efficient on the back-end and helps avoid typos.

=[Facility ID]@row + "- " + [Date Column]@row + IF(COUNTIFS([Facility ID]$1:[Facility ID]@row, [Facility ID]@row, [Date of Incident]$1:[Date of Incident]@row, [Date of Incident]@row) > 1, "-" + COUNTIFS([Facility ID]$1:[Facility ID]@row, [Facility ID]@row, [Date of Incident]$1:[Date of Incident]@row, [Date of Incident]@row))

Jessica Howell

@Paul Newcome Always so helpful! That worked.. thank you so much!

Paul Newcome

Happy to help! 👍️