COUNTA Function

Gos-C

Active Member
Joined
Apr 11, 2005
Messages
252
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Apr 11, 2005
Messages
252
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Apr 11, 2005
Messages
252

ADVERTISEMENT

Hi HOTPEPPER,

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

Thank you,
Gos-C
 

Watch MrExcel Video

Forum statistics

Threads
1,127,866
Messages
5,627,352
Members
416,244
Latest member
JBKool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top