Change cell reference every nth row and copy down

J

jms5679

New Member
Joined
Jan 10, 2011
Messages
4
I have a spreadsheet laid out like this:

Excel 2007
ABCDE
REGIONDATEWEIGHTQUANTITYPERCENTAGE
T0
TA
TB
TC
TD
TE
T0
TA
TB
TC
TD
TE
T0
TA
TB
TC
TD
TE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/13/2013[/TD]
[TD="align: right"] 67,681.00 [/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/13/2013[/TD]
[TD="align: right"] 6,525.00 [/TD]
[TD="align: right"]12[/TD]
[TD="align: right"] 0.10 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/13/2013[/TD]
[TD="align: right"] 46,464.00 [/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]68.65%[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1/13/2013[/TD]
[TD="align: right"] 145.00 [/TD]
[TD="align: right"]2158[/TD]
[TD="align: right"]0.21%[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1/13/2013[/TD]
[TD="align: right"] 1.00 [/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.00%[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1/13/2013[/TD]
[TD="align: right"] 14,546.00 [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21.49%[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1/14/2013[/TD]
[TD="align: right"] 92,056.00 [/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1/14/2013[/TD]
[TD="align: right"] 21,646.00 [/TD]
[TD="align: right"]243[/TD]
[TD="align: right"]23.51%[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1/14/2013[/TD]
[TD="align: right"] 21,645.00 [/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]23.51%[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]1/14/2013[/TD]
[TD="align: right"] 55.00 [/TD]
[TD="align: right"]484[/TD]
[TD="align: right"]0.06%[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1/14/2013[/TD]
[TD="align: right"] 2,164.00 [/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]2.35%[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1/14/2013[/TD]
[TD="align: right"] 46,546.00 [/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]50.56%[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]1/15/2013[/TD]
[TD="align: right"] 3,840.00 [/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]1/15/2013[/TD]
[TD="align: right"] 541.00 [/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]14.09%[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]1/15/2013[/TD]
[TD="align: right"] 2.00 [/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]0.05%[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]1/15/2013[/TD]
[TD="align: right"] 5.00 [/TD]
[TD="align: right"]566[/TD]
[TD="align: right"]0.13%[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]1/15/2013[/TD]
[TD="align: right"] 1,646.00 [/TD]
[TD="align: right"]1465[/TD]
[TD="align: right"]42.86%[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]1/15/2013[/TD]
[TD="align: right"] 1,646.00 [/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]42.86%[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E5[/TH]
[TD="align: left"]=C5/$C$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E6[/TH]
[TD="align: left"]=+C6/$C$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E7[/TH]
[TD="align: left"]=+C7/$C$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E8[/TH]
[TD="align: left"]=+C8/$C$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E9[/TH]
[TD="align: left"]=+C9/$C$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E11[/TH]
[TD="align: left"]=+C11/$C$10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E12[/TH]
[TD="align: left"]=+C12/$C$10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E13[/TH]
[TD="align: left"]=+C13/$C$10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E14[/TH]
[TD="align: left"]=+C14/$C$10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E15[/TH]
[TD="align: left"]=+C15/$C$10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E17[/TH]
[TD="align: left"]=+C17/$C$16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E18[/TH]
[TD="align: left"]=+C18/$C$16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E19[/TH]
[TD="align: left"]=+C19/$C$16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E20[/TH]
[TD="align: left"]=+C20/$C$16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E21[/TH]
[TD="align: left"]=+C21/$C$16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



I need to copy down the formulas in the percentage column, however the denominator in the formula only stays constant for 5 rows. I have 10,000 rows of data, so I can't do this manually. Is there a way to copy it down using a formula that will change the cell reference?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
HI,

how are C4, C10 and C16 related is there a relationship between them? Whats in E10 ?

It would be great to have a copy of the workbook to see better whats the best solution. I would say VBA is the way to go.

Thanks
 
Upvote 0
fredlo2008 said:
HI,

how are C4, C10 and C16 related is there a relationship between them? Whats in E10 ?

It would be great to have a copy of the workbook to see better whats the best solution. I would say VBA is the way to go.

Thanks
Click to expand...


Region T0 is the total for each set of data (TA - TE), so C4 is the total and C5 through C9. Likewise, the percentages in E5 -E9 add up to 100%. E10 is blank, as every entry in column E relating to region T0 would be because it is the total.
 
Upvote 0
Hello,

OK see if this code helps. Place it in a module and run it.


Code: 
Sub FillFormulas()

Dim i As Integer
Dim lrow As Integer
Dim rng As Range


Application.ScreenUpdating = False


    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each rng In Range("E1:E" & lrow)
        If rng.Value = vbNullString Then
            rng.Offset(1).Resize(5, 1).FormulaR1C1 = "=RC[-2]/R" & rng.Row & "C3"
        End If
    Next
    
Application.ScreenUpdating = True


End Sub

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
112
Peter_SSs
Peter_SSs
S
  • Question Question
need arrow based on cell color
2 3
Replies
26
Views
434
MARK858
MARK858
hardeep.kanwar
  • Question Question
Paste data based on cell color
Replies
2
Views
62
Peter_SSs
Peter_SSs
S
  • Question Question
Something going wrong in power pivot
Replies
0
Views
124
sharshra
S
D
  • Question Question
Pivot Table Conditioning Changes when expand/collapse
Replies
2
Views
291
dmj120
D

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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