Increment column value based on insert string

I have a large spreadsheet with a running list of different cells with
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 numbering
screen we have to keep correcting. Column D is a column where we
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 *SC03
BE ddd eee BE01
BR fgf sjs BR03
Sc fgf sjs [b]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 column
D entries below the insert based on the added Column A value as I have
shown above?

Thanks for all your help!


---
Message posted from http://www.ExcelForum.com/