If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Increment column value based on insert string
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 " wrote in message ... 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/ |
#2
|
|||
|
|||
Increment column value based on insert string
That is perfect for my needs.
Isn't the internet great? --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|