Sequential numbering

[tpedry]

I am trying to pull the data from column AA and enter it into column A without the period. I would also like to add the hyphen and the sequential 4 numbers to the end of each number to create the entire "code" number

Example:
Column AA = 02.140
Column A = 02140-0015
next row,
Column AA = 05.120 (or any number in my data source)
Column A = 05120-0016 (last four numbers is sequential from the row above in column A

See attached.

Thank you so much!

Tim

[Pete_UK]

Put this formula in A2:

=IF(AA2="","",SUBSTITUTE(AA2,".","")&"-"&TEXT(ROWS($1:15),"0000"))

then copy down.

Hope this helps.

Pete

[oeldere]

If you post an small excel file, you will get better help.

See the advice which is given in the threath below.

The solution will be the similar.

http://www.excelforum.com/excel-form...from-cell.html

[tpedry]

how did you get the -0015 to start at 15? I will need to adjust this number to when I am at right now........which is actually, -0020

[tpedry]

Sorry, I got that one figured out! Thank you so much Pete!

[Pete_UK]

ROWS($1:15) in the first formula will return 15, so just change it to ROWS($1:20) if you want to start your numbering from 20 (actually, the TEXT function turns it into 0020).

Hope this helps.

Pete

[tpedry]

I am getting circular reference errors between column A and AA. Can you figure out why?

[oeldere]

Use a smaller example, then you get better help.

why use a dropdownmenu and a formula in 1 cell (Z17 /AA17)

[tpedry]

I just realized it was still in there and I have removed it. I think that took care of this issue. I am also trying to populate column AB with a source, which would in turn populate column AA. Therefore I don't think I will need the dropdown box for AA. The dropdown box for Z is necessary for the speeding up the number selection from the source sheet.

You can see my other posts trying to get help with my other challenge.

I am not sure why you are asking me to post a smaller excel file. The file is what it is. If I changed it, it would break a lot of formulas.

Tim

[oeldere]

It take a lot of time to open it (if it even is possible).

So if I can't open it, I can't take a look and can't help.

[tpedry]

I am sorry. I can't make the file any smaller on this one.

I think I have all my problems solved however, so thank you for your help!!

Tim