Auto update my numbering system in excel

**gdallas** · 11-27-2009, 08:21 AM

Hi,

I would like Excel to control my numbering system in column A, Automatically. Im wondering if this can be done?

If for example I wanted to add a new item above row 19, to keep things sequential, I would want to make this number MCC1/xx/P008, however this would mean that I would have a duplicated number below it, i.e. the existing MCC1/xx/P008, which is currently on row 19.

My question is, If I was to do as I described, is there something I can implement that would auto revise the numbers below row 19 to avoid duplication and keeping things sequential?

Thanks, I hope I made sense!

**teylyn** · 11-27-2009, 08:55 AM

use concatenation to dynamically create a number based on the row, e.g.

="MCC1/"&ROW()&"P008"

assuming that the xx part is the number that changes and your data starts in row 2. then just copy that formula into the cells that need the sequential number. If you insert a row, they should update

If that does not help, post a workbook with some sample data. We're great with workbooks, but we don't like pictures very much ....

hth

**Jack in the UK** · 11-27-2009, 09:26 AM

Your need a triger - generally i use Column or Rows to line up data, can this not be used

=column()
=row()

And make some use that way ?

**gdallas** · 11-27-2009, 09:42 AM

Hey thanks for the response.

Actually, its the P008 part that i want to increment. But not nesseaarilly looking to tie it on to the row its on, if you know what i mean.

any ideas?

**teylyn** · 11-27-2009, 09:45 AM

Tie it to what , then?

The next logical number? Then maybe (if the formula runs in column C, otherwise adjust

="MCC1/xx/P"&TEXT(COUNTA($C$2:C2)+1,"000")

and copy down

nest in an if statement to make showing the result dependent on a value in another column in the same row, like

=IF(ISTEXT(A2),"MCC1/xx/P"&TEXT(COUNT($C$2:C2)+1,"000"),"")

**gdallas** · 11-27-2009, 09:47 AM

maybe im just asking for too much!

it would also be good to be able to insert a blank row if i wanted too, whcih i wouldnt want effecting the numbering system.

**teylyn** · 11-27-2009, 09:59 AM

I edited my post while you replied. check again for changes, and, again, if this does not help post an example! That will take the guesswork out of the equation. Define what you want to see where and provide the logic and reasoning behind it. I would be surprised if it could not be solved here.