Sequence numbering based on another Cells info.

Jake W

I'm looking for a formula to sequence document numbers based on another cells info. I've set up IF functions to code each main column (in blue) which will be hidden in the background & locked to which i then used a JOIN function to get a serial code. (shown in "document Code").

With those in place, On "Document No." Column - I'm looking for a formula to create sequences numbers based on the Document Code Column. As i have multiple RA-LOGIH. I want this to be RA-LOGIH001 & the one below LOGIH002 not broken down in sequence due to each row as shown on "Document No." (if this makes sense).

I've created on the sheet in Orange - What i want.

So the user only needs to select "Document Type" -> "Department" -> "Dept. Coverage" & this will automate a Document number based on previous entries.

Paul Newcome

You would drop that field in to the first portion similar to how we strung together the Doc Cod and Dept Code. Then you would add in another range/criteria set to the COUNTIFS to follow the same syntax as both the Doc and Dept Codes.

Paul Newcome

You will first need to insert an auto-number column with no special formatting (called "Auto" in this example). Then you would use something like this:

=[Doc Code]@row + "-" + [Dept Code]@row + RIGHT("000" + COUNTIFS([Doc Code]:[Doc Code], @cell = [Doc Code]@row, [Dept Code]:[Dept Code], @cell = [Dept Code]@row, Auto:Auto, @cell<= Auto@row), 3)

Jake W

Hi Paul,

this worked wonders except it's not picking up dept. Cov Code? If you can include the Dept. Cov code as well - This would be exactly what i need.

Thank you for replying by the way - You have no idea how long i've been trying to wrap my brain around this one.

Paul Newcome

You would drop that field in to the first portion similar to how we strung together the Doc Cod and Dept Code. Then you would add in another range/criteria set to the COUNTIFS to follow the same syntax as both the Doc and Dept Codes.

Jake W

That Worked mate.

Thank you - Really appreciate you help with this mate.

Paul Newcome

Happy to help. 👍️