Increment column value based on insert string

[Bharnick]

I have a large spreadsheet with a running list of different cells wit
one column being the key for a numbering scheme. So, for example:

Column A is the key with only one of 4 values: <null>, “Sc”,”BR”,”BE”

We insert data into this sheet regularly, but there is a numberin
screen we have to keep correcting. Column D is a column where w
constantly are updating this running list, based on column A. So:

Current sheet:
___A_______B_______C_______D_
Code Data1 Data2 Final Code
Sc xxx yyy Sc01
BR zzz aaa BR01
BR bbb ccc BR02
Sc xxx yyy Sc02
BE ddd eee BE01
BR fgf sjs BR03
Sc fgf sjs Sc03

Insert another Sc after row 3 and increment all the Sc#’s below it:
___A_______B_______C_______D_
Code Data1 Data2 Final Code
Sc xxx yyy Sc01
BR zzz aaa BR01
SC NEW DATA SC02[/B]
BR BBB CCC BR02
SC XXX YYY *SC0
BE ddd eee BE01
BR fgf sjs BR03
Sc fgf sjs Sc04*

I have bolded the data I have to change when I do this insert

Is there any formula I can use in Column D to auto-increment the colum
D entries below the insert based on the added Column A value as I hav
shown above?

Thanks for all your help

 

[A.W.J. Ales]

Bharnick,

Try =A1&COUNTIF($A$1:A1,A1) in cell D1 and copy down.
If you insert a row, you have (offcourse) to copy this formula to the cell
in column D in that row as well.

(If you need the number in two positions exact (SC01 rather than SC1), use
=A1&TEXT(COUNTIF($A$1:A1,A1),"#00")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bharnick]

That is perfect for my needs.

Isn't the internet great?