Formula to automatically assign the next sequential number to documents in an Excel register
I'm creating a register in Excel to number engineering documents. Each document will have a unique number and will follow this numbering structure:
Discipline Code - Document Type - Sequential Number
Example: ELE-DRW-0001. This is the 1st electrical drawing in the register.
I'm trying to find the formula that will assign the next sequential number to new documents depending on their discipline code and document type code.
Example: If I am registering a new electrical drawing, I'd like Excel to give it 0002 as the sequential number.
If I am registering a mechanical drawing, however, it has to be number 0001 since it is the first mechanical drawing I am registering.
I hope my explanation is clear to you guys. I'm sure there is a solution with formulas to help me with this.
Thank you very much in advance for your help.
- zribaesc's blog
- Login or register to post comments
- 11454 reads
Hi zribaesc, Create 2 excel
Hi zribaesc,
Create 2 excel sheets.
1st sheet:
Make the first one your "data entry" sheet where you can enter the details like item description, date acquired and more. Add a column sequential number, this is where we input the formula to automatically create a numbering based on the item description.
2nd sheet:
Name the 2nd sheet "Reference", create a table where you can enter the document type and the discipline code. Define name for the document type - this will be used as a drop down list for sheet 1 - item description column.
Enter this formula in the data entry sheet-sequential number column
=IFERROR(IF(LEN(COUNTIF($C$7:$C7,C7))=1,CONCATENATE(VLOOKUP(C7,Reference!B:C,2,FALSE),"000",COUNTIF($C$7:$C7,C7)),IF(LEN(COUNTIF($C$7:$C7,C7))=2,CONCATENATE(VLOOKUP(C7,Reference!B:C,2,FALSE),"00",COUNTIF($C$7:$C7,C7)),IF(LEN(COUNTIF($C$7:$C7,C7))=3,CONCATENATE(VLOOKUP(C7,Reference!B:C,2,FALSE),"0",COUNTIF($C$7:$C7,C7)),COUNTIF($C$7:$C7,C7)))),"")
If this is not clear, I can send you the excel file I created for this. Message me at fdelossantos25@gmail.com
Hope this help you.
sequencing
Hello
I found your document sequencer at excelexpert.com and was trying to set up the lockup tables but got lost. Can you send me any hints on the process, Im just trying to do a document register for incoming documents and reports. My idea is to use just one code description and date: example Admin150321-000001(2015/3/21). This would limit the hierarchy and allow for post dating older documents to recall on a search.
Any advice is welcome
Thank you