bvanscoy678
Member
Hello,
I am using this formula to count the number of unique values in a masterlist. Since I am also counting the heading, it returns the next number I can use as a document unique ID# when I create a new document. (There are two visible worksheet in attached workbook. The below formula is in the createtransfertemplate!B6)
This is the formula I need to modify, so the numbers show up when I copy them (via code) to the masterlist! A column
=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&SUMPRODUCT((NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")))
The only problem is that it returns a value with no leading zeros. I manually entered the -001,-002 but when I use the VBA Code within the workbook, my formula above gives me the count, but no leading zero's. I would like to always have 3 numbers, so it looks uniform and lines up (I'll never have more than 999). The next one in line will show AT13-6, but I would like to see AT13-006. I hope that makes sense.
This is the A column from MasterList! (attached workbook)
TransferID
AT13-001
AT13-001
AT13-002
AT13-002
AT13-002
AT13-003
AT13-4
AT13-5
I have thought about splitting the columns, formatting as text, then use leading zeros. I would then need to bring it all back together again. I thought there might be another way with the same worksheet layout.
Thank you in advance for any help.
I did try this, but it was wrong:
=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&text(SUMPRODUCT((NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")),000)
https://www.dropbox.com/s/0alr5t1pf86qc74/chandoo%20sumproduct.xlsm
I am using this formula to count the number of unique values in a masterlist. Since I am also counting the heading, it returns the next number I can use as a document unique ID# when I create a new document. (There are two visible worksheet in attached workbook. The below formula is in the createtransfertemplate!B6)
This is the formula I need to modify, so the numbers show up when I copy them (via code) to the masterlist! A column
=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&SUMPRODUCT((NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")))
The only problem is that it returns a value with no leading zeros. I manually entered the -001,-002 but when I use the VBA Code within the workbook, my formula above gives me the count, but no leading zero's. I would like to always have 3 numbers, so it looks uniform and lines up (I'll never have more than 999). The next one in line will show AT13-6, but I would like to see AT13-006. I hope that makes sense.
This is the A column from MasterList! (attached workbook)
TransferID
AT13-001
AT13-001
AT13-002
AT13-002
AT13-002
AT13-003
AT13-4
AT13-5
I have thought about splitting the columns, formatting as text, then use leading zeros. I would then need to bring it all back together again. I thought there might be another way with the same worksheet layout.
Thank you in advance for any help.
I did try this, but it was wrong:
=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&text(SUMPRODUCT((NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")),000)
https://www.dropbox.com/s/0alr5t1pf86qc74/chandoo%20sumproduct.xlsm