Excel 2007 counting numbers

[urlocaljeweler]

In Excel 2007 I have a question...

I have a cell in a column B1:
MJTB1001

I need to paste to approximately 235 new rows in the column B1 as follows

MJTB2002
MJTB3003
MJTB4004... etc.

What formula should I use to accomplish this task?

Also I have the cell MJTB2044 and I need to make 235 new cells in column to
fit like this...

MJTB2044
MJTB3044
MJTB4044
MJTB5044 ... etc... 235 times....

Any thoughts?

Thank you.

 

[Peo Sjoblom]

In B2 put


="MJTB"&1001+(ROWS($B$1:B1)*1001)


copy down 235 rows, select the range and copy and paste special as values in
place



for the second use


="MJTB"&2044+(ROWS($B$1:B1)*1000)


repeat the copy and pasting.


Note that $B$1:B1 has nothing to do with the firs value being in B1 it just
generates row numbers

--


Regards,


Peo Sjoblom

 

[Bill Kuunders]

one way.................

create 4 columns MJTB 2 00 2

extend MJTB all the way down
extend 00 all the way down
extend the other two columns from 2 to 235

concatenate the 4 columns into one
do the edit, copy, edit, paste special, values routine

similar solution for the second question

Greetings from New Zealand

 

[Gord Dibben]

Since you neglected to show what row 235 should be I can only guess.

Do you want the "00" retained all the way down?

="MJTB" & ROW() & "00" & ROW() entered in B1 returns 23500235

Or simply format to "000" as you progress?

="MJTB"&ROW()&TEXT(ROW(),"000") returns 235235

I would guess the latter.

For second scenario of MJTB2044 same guesses.


Gord Dibben MS Excel MVP