fromula for repetitive task

[shodan]

Hi

I need some help to find a good formula to carry out a repetitive task:

I have a sheet with 24 rows (and 6 col) with different items. they loo like this:

P200NP048  STDRAWMAT P20 M200601 AOP1 000

I need to copy them down 12 times and than rename the month. I 'm not looking for a vba code !

the items don't change order, (so at row 25 this item shown above returns and so on...) but note that at month 10, the month will look like M200610.


I can make it work with a vba code, but I wanted to try it with excel formula.

Regards,

 

[erik.van.geit]

Hello, Shodan,

so basically you want to change this
M200601
to
M200602
M200603
etcetera

A1: M200601
formula

=LEFT(A1,5)&TEXT(RIGHT(A1,2)+1,"00")

does this work for you ?
kind regards,
Erik

 

[shodan]

Eric,

thanks, but that does not completely does the thing. IT works for the second month, .
to be more specific:

My fist 24 rows stay at M200601
Than, applying your formula, as of row 25, returns this M200602 which is good, but, than the next set of 24 rows should be altered in M200603, so it should should be automated somehow that it can add up another number than 1, after a specified number of rows.

Is this clear??

 

[Peter_SSs]

Eric,

thanks, but that does not completely does the thing. IT works for the second month, .
to be more specific:

My fist 24 rows stay at M200601
Than, applying your formula, as of row 25, returns this M200602 which is good, but, than the next set of 24 rows should be altered in M200603, so it should should be automated somehow that it can add up another number than 1, after a specified number of rows.

Is this clear??

Not to me.

It seems:
The first 24 rows should all be M200601
Row 25 should be M200602

Questions:
How many rows should show M200602?
What is the row number of the first row to show M200603?
What is the row number of the first row to show M200604?
When we eventually get to the end of M200612 (at which row?), is the next row after that M200701?

 

[erik.van.geit]

yes it was clear to me,
but I thought you only struggled with the replace part
try this

="M2006" & TEXT(FLOOR(ROW()/24,1)+1,"00")

using the same logic you can make "2006" update to 2007 ...
you may want to replace "M2006" by a cellreference ...

best regards,
Erik

 

[Peter_SSs]

Erik

If this is along the lines of what the OP wants, you may need a slight adjustment as this only gives M200601 for the first 23 rows.

 

[erik.van.geit]

Of course, Peter,

="M2006" &TEXT(FLOOR((ROW()-1)/24,1)+1,"00")

thanks for mentioning that !