Sequence numbers based on date and independent of column sort order

Hi,

I set up an auto sequence number (column B) based on the date entries in column A (see example below) using the following formula:
=IF(ISBLANK(A45),"","PERM"&"_"&TEXT(A45,"YYYYMMDD")&"_"&TEXT(COUNTIF($A$5:A45,A45),"0"))

It all works fine, until I start to re-sort the data entries by the various columns. Then, an entry that had previously assigned sequence number PERM_YYYYMMDD_3 maybe suddenly become PERM_YYYYMMDD_1.

Is there a formula that will allow me to assign a permanent unique sequential number independent of the sort order?

Column A Column B
12/02/2014 PERM_20140212_1
12/02/2014 PERM_20140212_2
12/02/2014 PERM_20140212_3

Thanks so much for your help!
Barbara

Hi AlKey,

yes, that's the formula I've been using, and the sequential numbers change according to the sort order of the column, but I want them to stay the same once they've been assigned independent of sort order.
Thanks,
Barbara

ok, do I need to re-post this in part of the forum that deals with VBA?

Hi Izandol,

thanks for getting back to me. I did as instructed, i.e. pasted code, saved as macro-enabled file, but I'm not sure if something's happening in the sheet. I apologize, I'm not too familiar with VBA programming. Do I need to do anything else apart from the above? Does this VBA code replace all the formulas I had been using previously, and do I need to delete them off my sheet? Sorry again and thanks for any further assistance, Barbara

I tried (both by Alt+F8 and by going via Developer to the Macros tab), but the window is empty, there are no macros listed for this workbook.

thanks jmac1947, that will help.

I still need to figure out how to create the sequential number in column Q, and not B. Any idea how the code needs to be changed? I haven't really figured out how the R1C1 reference style works.

Thanks so much!
B

thanks Izandol, that works. I'm afraid I need to bug you one more time as I realized that the sequencing only works when I fill in the dates a descending order, but not in a random order.

For example, if I have forgotten to add an item at a particular date, and want to add it later on by inserting a row above, it won't assign the next following sequence number. Is there a way to make the system recognize that there are already items for the same date no matter in which row they are?

Thanks,
Barbara