Sequence numbering based on another Cells info.

Jake W · 01/29/24

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 · 01/30/24

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 · 01/29/24

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 · 01/30/24

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 · 01/30/24

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 · 01/30/24

That Worked mate.

Thank you - Really appreciate you help with this mate.

Paul Newcome · 01/31/24

Happy to help. 👍️

Sequence numbering based on another Cells info.

Best Answer

Answers

Help Article Resources

Categories