Advance formula 3 columns

[Kim B]

Hi all. I need help with adjusting every three columns for cell ref below. Everything else in the formula stays the same


Starting in column O, the formula is copied into R and then U and so on.
=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!O$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

needs to read when copied to column R

=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!R$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)


Starting in column P, the formula is copied into S and so on.
=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A,'by Store Data Pull 1-9'!P$4,'Forecast 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

needs to read when copied to column S

=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A,'by Store Data Pull 1-9'!S$4,'Forecast 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

Thanks for any help

 

[dreid1011]

When you copy/paste the cell into each new column, the column reference you highlighted should update automatically. But you have to copy/paste the cell, do not copy the formula from the formula bar.

Or do you have so many columns to paste it into that doing so manually is not efficient?

How many columns and rows do you need the formulas copied into?

 

[etaf]

doesnt that do that automatically - as the cell is set to a relative reference - NO $ in front of the column id - P so when copyied into Q it will change to Q as yo have shown

 

[Kim B]

When you copy/paste the cell into each new column, the column reference you highlighted should update automatically. But you have to copy/paste the cell, do not copy the formula from the formula bar.

Or do you have so many columns to paste it into that doing so manually is not efficient?

How many columns and rows do you need the formulas copied into?

the spreadsheet goes from column O to column AVH 40 rows in each column

 

[dreid1011]

Which row is the formula being pasted into?

Is the formula being copied to a single row, or for the entire range of 40 rows? And if the entire range, what part of the formula needs to change for each row?

 

[Kim B]

Which row is the formula being pasted into?

Is the formula being copied to a single row, or for the entire range of 40 rows? And if the entire range, what part of the formula needs to change for each row?

O4 is where it starts it needs to be copied across every 3 until column AVH 40 rows down.

 

[dreid1011]

So O4:AVH43. As for the other part of the question, the column you highlighted updates for each column, but does anything update for each row?

 

[Kim B]

So O4:AVH43. As for the other part of the question, the column you highlighted updates for each column, but does anything update for each row?

$A6 is the only value that advances by the row.

=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!O$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

 

[dreid1011]

$A6 is the only value that advances by the row.

=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!O$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

Okay, I have the code at work mostly ready except for the row adjustment. But I am not at work today so I can get it to you tomorrow.

 

[dreid1011]

See if this does what you want. Try it on a copy of your workbook:

Private Sub EnterFormula()
Dim frm1 As String, frm2 As String
Dim ws As Worksheet
Dim i As Long, j As Long

Set ws = Sheets("Sheet3")

Application.ScreenUpdating = False
For i = 15 To 1256 Step 3
    j = i + 1
    frm1 = "=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!R4C,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!R[2]C1)"
    With ws.Range(ws.Cells(4, i), ws.Cells(43, i))
        .Formula2 = frm1
    End With
    frm2 = "=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A,'by Store Data Pull 1-9'!R4C,'Forecast 1-9'!$B:$B,'by Store Data Pull 1-9'!R[2]C1)"
    With ws.Range(ws.Cells(4, j), ws.Cells(43, j))
        .Formula2 = frm2
    End With
Next i
Application.ScreenUpdating = True
End Sub