Copying or filling down formulas with cell references when skipping rows.

j_weird

New Member
Joined
Mar 11, 2025
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am working on a code generator where I have x rows of data where row 1-2 only has a formula in column A, Row 2 has Text in Column B only, rows 3-10 (for example has formulas in Column C. I have to copy or fill down these x lines of code 80 times but I am not finding a way to index the cell/sheet references once per instance since I'm copying many rows at a time. Attached image is showing formulas as an example. Second image is format of reference cells on the sheet 3. Looking for a way to copy or fill these down so the cell reference in the second set all point to row 3. Third set to row 4 and so on.

Please ignore the fact that the formulas in column C are all the same, my real formulas are different but this should illustrate my issue.
 

Attachments

  • ExcelImg.png
    ExcelImg.png
    9 KB · Views: 5
  • ExcelImg2.png
    ExcelImg2.png
    6.9 KB · Views: 5
Welcome to the Forum!

Your question is not particularly clear, but perhaps this might get you started?

ABCDE
1Set1FormulaPoint to ...
2BlahBlahRow 1
3Row 1Row 2
4Row 1Row 3
5Row 1
6Row 1
7Row 1
8Row 1
9Row 1
10Row 1
11Set2
12BlahBlah
13Row 2
14Row 2
15Row 2
16Row 2
17Row 2
18Row 2
19Row 2
20Row 2
21Set3
22BlahBlah
23Row 3
24Row 3
25Row 3
26Row 3
27Row 3
28Row 3
29Row 3
30Row 3
31
Sheet1
Cell Formulas
RangeFormula
C3:C32C3=LET(N,10,gap,2,P,E2:E4,s,SEQUENCE(ROWS(P)*N,,0),x,IF(MOD(s,10)>=N-gap,"",INDEX(P,1+INT(s/10))),x)
Dynamic array formulas.

Please ignore the fact that the formulas in column C are all the same, my real formulas are different but this should illustrate my issue.
Generally it's better if you provide the detail up-front, rather than over-simplifying.
 
Upvote 0
Welcome to the Forum!

Your question is not particularly clear, but perhaps this might get you started?

ABCDE
1Set1FormulaPoint to ...
2BlahBlahRow 1
3Row 1Row 2
4Row 1Row 3
5Row 1
6Row 1
7Row 1
8Row 1
9Row 1
10Row 1
11Set2
12BlahBlah
13Row 2
14Row 2
15Row 2
16Row 2
17Row 2
18Row 2
19Row 2
20Row 2
21Set3
22BlahBlah
23Row 3
24Row 3
25Row 3
26Row 3
27Row 3
28Row 3
29Row 3
30Row 3
31
Sheet1
Cell Formulas
RangeFormula
C3:C32C3=LET(N,10,gap,2,P,E2:E4,s,SEQUENCE(ROWS(P)*N,,0),x,IF(MOD(s,10)>=N-gap,"",INDEX(P,1+INT(s/10))),x)
Dynamic array formulas.


Generally it's better if you provide the detail up-front, rather than over-simplifying.
Unfortunately I cannot share the actual sheet contents. ExcelImg image show the formulas with just specific string content changed or omitted but the formulas are all CONCAT formulas to build test strings using cell references from another sheet which is the second image.

I don't quite follow how to combine your solution with my current implementation.

In the first image rows 6-14 are showing identical formulas but in the actual project they are all different. They are all CONCAT functions but each is used to create a different text string (the code I mentioned in the beginning).

I am needing to copy or repeat those 14 rows x numbers of times. The first 14 are using values from row 2 in sheet 3, the second 14 would use values in row 3 in sheet 3 and so on.

I added a new example showing the actual issue I'm having with the copying of these first 15 rows. When copying 15 down the row index used for reference cells is taking the row count of the second row (not sure why it's not using 17 but that is another problem) in the copied group. So instead of all reference cells pointing to row 3 (or B3 and C3 etc.) they all point to row 18.
 

Attachments

  • ExcelImg3.png
    ExcelImg3.png
    55.6 KB · Views: 3
Upvote 0
A couple of ideas you should be able to adapt.

I have set this up for only three rows:

A1: =INDEX(Sheet7!B$2:B$4,1+(ROWS(A$1:A1)-1)/16)
C6: =LET(N,3,M,10,gap,16,r,1+(ROWS(C$6:C6)-1)/gap,data,Sheet7!B$2:INDEX(Sheet7!B:B,N+1),INDEX(data,r)&" Alarms[5]:=Test Text "&SEQUENCE(10,,1+M*(r-1)))

Change these parameters appropriately, and you can copy A1:C16 down as many 16 row blocks as required.

ABC
1Hello
2
3
4
5
6Hello Alarms[5]:=Test Text 1
7Hello Alarms[5]:=Test Text 2
8Hello Alarms[5]:=Test Text 3
9Hello Alarms[5]:=Test Text 4
10Hello Alarms[5]:=Test Text 5
11Hello Alarms[5]:=Test Text 6
12Hello Alarms[5]:=Test Text 7
13Hello Alarms[5]:=Test Text 8
14Hello Alarms[5]:=Test Text 9
15Hello Alarms[5]:=Test Text 10
16
17Welcome
18
19
20
21
22Welcome Alarms[5]:=Test Text 11
23Welcome Alarms[5]:=Test Text 12
24Welcome Alarms[5]:=Test Text 13
25Welcome Alarms[5]:=Test Text 14
26Welcome Alarms[5]:=Test Text 15
27Welcome Alarms[5]:=Test Text 16
28Welcome Alarms[5]:=Test Text 17
29Welcome Alarms[5]:=Test Text 18
30Welcome Alarms[5]:=Test Text 19
31Welcome Alarms[5]:=Test Text 20
32
33Goodbye
34
35
36
37
38Goodbye Alarms[5]:=Test Text 21
39Goodbye Alarms[5]:=Test Text 22
40Goodbye Alarms[5]:=Test Text 23
41Goodbye Alarms[5]:=Test Text 24
42Goodbye Alarms[5]:=Test Text 25
43Goodbye Alarms[5]:=Test Text 26
44Goodbye Alarms[5]:=Test Text 27
45Goodbye Alarms[5]:=Test Text 28
46Goodbye Alarms[5]:=Test Text 29
47Goodbye Alarms[5]:=Test Text 30
48
Sheet1
Cell Formulas
RangeFormula
A1,A33,A17A1=INDEX(Sheet7!B$2:B$4,1+(ROWS(A$1:A1)-1)/16)
C6:C15,C38:C47,C22:C31C6=LET(N,3,M,10,gap,16,r,1+(ROWS(C$6:C6)-1)/gap,data,Sheet7!B$2:INDEX(Sheet7!B:B,N+1),INDEX(data,r)&" Alarms[5]:=Test Text "&SEQUENCE(M,,1+M*(r-1)))
Dynamic array formulas.

AB
1
2Hello
3Welcome
4Goodbye
5
Sheet7
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top