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.

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


"urlocaljeweler" wrote in message
...
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.

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

"urlocaljeweler" wrote in message
...
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.

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

On Mon, 23 Nov 2009 11:26:09 -0800, urlocaljeweler
wrote:

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.