How to copy multiple rows, and change the reference cell

[Doc.Blade]

Hello Everyone,

Been searching for a few days, and I cant seem to find an answer that works for me unfortunately.

I am trying to create an accounting sheet that will combine data for me.

I have cells for all the data, then I have a column of cells that I need the data sorted in a specific manner. I then need to replicate this 250 times, and I dont really want to manually change the reference cell value 1500 times (6 rows of data x 250) ... lol


The forumula I have for the first 6 rows is:

="D"&A3
="U"&D3
="T"&D3
="P"&B3
="M"&C3
^

What I need to do is replicate this 249 more times (all 6 rows), but I need to change the reference cell value up by one on each replication. So basically, the next 6 lines needs to be:

="D"&A4
="U"&D4
="T"&D4
="P"&B4
="M"&C4
^

Then the next 6 rows:

="D"&A5
="U"&D5
="T"&D5
="P"&B5
="M"&C5
^

Rinse and repeat until I have replicated all 6 rows 250 times in total.


These all need to be one after another all in the same column.


Higlighting all 6 rows, and dragging down changes the reference cell value, but changes it by 5, not 1 (so ="D"&A3 changes to ="D"&A8, and not ="D"&A4, for example).


Any help anyone can offer would be greatly appreciated, as I really dont want to sit there and manually edit the formulas 1500 times ... hehe.


Many thanks in advance for any help anyone can offer.

Rick

[Special-K]

This should work
You'll need to copy it down until you get to the 249 or 250 limit 5*250 = around 750 rows I guess

NOTE: DO NOT CHANGE THE ORDER OF THE LETTERS - THEY ARE CORRECT (even though the order is different to the order you stated - this is because LOOKUP needs things in alphabetical/numerical order with 0 first, MOD(...,5) will produce a 0 on every 5th line generated therefore the 0 must be moved to the first LOOKUP value so it works correctly. Consequently the results for a LOOKUP of 0 - M and C - should also be moved to the first resultant value).

=LOOKUP(MOD(ROWS($1:1),5),{0,1,2,3,4},{"M","D","U","P","T"})&INDIRECT(LOOKUP(MOD(ROWS($1:1),5),{0,1,2,3,4},{"C","A","D","D","B"})&INT((ROWS($1:1)-1)/5)+3)

[Doc.Blade]

Thank you everyone for your responses.


The one that I had the best success with (with my limited knowledge of excel), was this one below. It seemed to work brilliantly, many thanks, but the only problem is the formula did not include adding in the ^ on the 6th line. This is very important to be there to separate the transactions. Any help you can offer to fix the formula to add the ^ in would be greatly appreciated.

This should work
You'll need to copy it down until you get to the 249 or 250 limit 5*250 = around 750 rows I guess

NOTE: DO NOT CHANGE THE ORDER OF THE LETTERS - THEY ARE CORRECT (even though the order is different to the order you stated - this is because LOOKUP needs things in alphabetical/numerical order with 0 first, MOD(...,5) will produce a 0 on every 5th line generated therefore the 0 must be moved to the first LOOKUP value so it works correctly. Consequently the results for a LOOKUP of 0 - M and C - should also be moved to the first resultant value).

=LOOKUP(MOD(ROWS($1:1),5),{0,1,2,3,4},{"M","D","U","P","T"})&INDIRECT(LOOKUP(MOD(ROWS($1:1),5),{0,1,2,3,4},{"C","A","D","D","B"})&INT((ROWS($1:1)-1)/5)+3)

Many thanks again,

Rick

[Akuini]

Using VBA.
Select those 6 cells then run this code:

Sub b1289272b()
'https://www.excelforum.com/excel-general/1289272-how-to-copy-multiple-rows-and-change-the-reference-cell.html
Dim c As Range, x As Range
    
    For Each x In Selection
    x = Replace(x.Formula, "=", "")
    Next
    
Set c = ActiveCell.Resize(Selection.Cells.count * 250)
Selection.AutoFill Destination:=c, Type:=xlFillDefault
    
    For Each x In c
    x = "=" & x.Value
    Next

End Sub

[José Augusto]

Hi

Another way to do that avoiding the use of INDIRECT

=MID("DUTPM",MOD(ROW(A3)-ROW($A$3),5)+1,1)&INDEX(A:D,QUOTIENT(ROW(A3)-ROW($A$3),5)+ROW($A$3),

INDEX({1,4,3,2,3},MOD(ROW(A3)-ROW($A$3),5)+1))

where A3 indicate the row of start of data.

[Akuini]

The code in post 3 didn't work for you?

[José Augusto]

Hi

I do not see the last row of your sample with the char ^.

See the correction

=IFERROR(MID("DUTPM ",MOD(ROW(A3)-ROW($A$3),6)+1,1)&INDEX(A:D,QUOTIENT(ROW(A3)-ROW($A$3),6)+ROW($A$3),

INDEX({1,4,3,2,3,0},MOD(ROW(A3)-ROW($A$3),6)+1)),"^")

[Doc.Blade]

Hello José,

Many thanks for that. Was a slight typo in the code, but guessed how to fix it and it worked

This is what worked:

=IFERROR(MID("DUTPM ",MOD(ROW(A3)-ROW($A$3),6)+1,1)&INDEX(A:D,QUOTIENT(ROW(A3)-ROW($A$3),6)+ROW($A$3),INDEX({1,4,4,2,3,0},MOD(ROW(A3)-ROW($A$3),6)+1)),"^")

After the index, had to change it from {1,4,3,2,3,0} to {1,4,4,2,3,0} and all seemed to work fine.

Many thanks for that, much appreciated.

Thank you to Special-K and Akuini too, very much appreciate everyone's help

Rick

[José Augusto]

You are welcome.

Thanks for the feedback.