COUNTA Function

[Gos-C]

Hi everyone,

I need your help.

The following formula is in cell A4, copied down to cell A8:

=IF(D4<>"",MID(YEAR(D4),4,1)&TEXT(MONTH(D4),"00")&MID(C4,1,1)&"-"&MID(B4,1,1)&MID(B4,FIND(" ",B4)+1,1)&TEXT(COUNTA($D$4:D4),"000"),"")

The following names are in range B4:B8:

Jane Doe
Sister Rock
David ****
Sussie Jay
Tom Jones

The following two-digit codes are in range C4:C8:

AB
XY
JT
PQ
EF

Cell D5 contains the date:
04-Aug-07

Cell D7 contains the date:
21-Aug-07

As a result, cell A5 shows 708X-SR001 and cell A7 shows 708P-SJ002.

If I enter 23-Aug-07 in cell D4, cell A4 would show 708A-JD001, and cells A5 and A7 would change to 708X-SR002 and 708P-SJ003, respectively, but I need cell A4 to show 708A-JD003 and cells A5 and A7 to retain their original values.

Any help, please.

Thank you,
Gos-C

 

[Scott Huish]

First there's a couple of things with your formula that aren't necessary, and some things that can shorten it.

=IF(D4,RIGHT(YEAR(D4))&TEXT(D4,"mm")&LEFT(C4)&"-"&LEFT(B4)&MID(B4,FIND(" ",B4)+1,1)&TEXT(COUNT($D$4:D4),"000"),"")

TEXT(MONTH(D4),"00") for example will return the same value as TEXT(D4,"mm")
You may also want to look at the LEFT and RIGHT functions. In that formula it looks like you are basing your last number on how many dates are in D4:D8, as such COUNT would work.

However, it will take VBA code to change it from the formula to the value once a value in D4:D8 has been changed. What do you want to have happen if a value in D4:D8 is deleted? Put the formula back?

 

[Gos-C]

Hi HOTPEPPER,

Thanks for the tips - I always thought the first part of my IF statement must have =, <>, > or <.

The LEFT and RIGHT funtions are correct, and the three-digit number at the end is based on the number of dates when the date for that record is entered. In other words, when the first date is entered, the last three digits should be 001. When the next date is entered, the last three digits should be 002, etc. Therefore, I need to replace the COUNT function part of the formula in order to achieve that.

Hope this explains it more clearly.

Thanks,
Gos-C

 

[Scott Huish]

It will take VBA to lock the values. Again, what do you want to do if a date is deleted in Column D? And what is the actual range you are using, or do you really only have 5 cells that you are doing this with?

 

[Gos-C]

Hi HOTPEPPER,

Yes, if the date is deleted, put back the formula. The actual range is A4:AA1505.

Thank you,
Gos-C

 

[Gos-C]

Hi everyone,

I still need help with this.

Any help?

Gos-C

 

[Gos-C]

Any help?

Gos-c