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

 

[Kim B]

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

works perfectly! thank you so much

 

[dreid1011]

works perfectly! thank you so much

You're welcome and happy to help.

 

[Kim B]

You're welcome and happy to help.

I don't want to be greedy but could I ask for a modification...

Staying with this same formula..change the highlighted red reference to match the blue date. The blue date is a tab and is sequential representing month-day. So there is a tab for each day of the month labeled "by Store Data Pull mm-dd" In this example it happens to be 1-15. I need the paste formula to match the mm-dd tab reference

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

 

[dreid1011]

I don't want to be greedy but could I ask for a modification...

Staying with this same formula..change the highlighted red reference to match the blue date. The blue date is a tab and is sequential representing month-day. So there is a tab for each day of the month labeled "by Store Data Pull mm-dd" In this example it happens to be 1-15. I need the paste formula to match the mm-dd tab reference

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

Okay, then which of these would be correct?

1) 'by Store Data Pull 1-15' --> 'Paste CSV File Here 1-15'
2) 'by Store Data Pull 1-15' --> '1-15'

And how is the blue date portion being changed? If you are changing it manually, would it not be just as easy to change the red part manually at the same time? The formulas are hard coded into the code.

Is your goal for this to run on every tab at the same time (tab names 'by Store Data Pull mm-dd') and update the red part for each tab?

 

[Kim B]

Okay, then which of these would be correct?

1) 'by Store Data Pull 1-15' --> 'Paste CSV File Here 1-15'
2) 'by Store Data Pull 1-15' --> '1-15'

And how is the blue date portion being changed? If you are changing it manually, would it not be just as easy to change the red part manually at the same time? The formulas are hard coded into the code.

Is your goal for this to run on every tab at the same time (tab names 'by Store Data Pull mm-dd') and update the red part for each tab?

The blue date is being changed manually on each tab as well. Being 1200+columns, it became monotonous to change each column. So the next tab would be by store data pull 1-16 so the formula within that tab would have to reference that tab and there would be that many tabs for days in the month, so the case of January, there would be 31 tabs labeled by store data pull x-xx. Only the csv file reference would need to be changed on each store data pull tab. The store data pull tab is already preset but the csv file reference needs to match the store date pull tab within the store data pull tab. We only work within the store data pull tab, the csv tabs are just data imported and then used as a reference to pop data into the store tab. Of the two macros, the pull data is the most tedious so the most important. Sorry for the confusion and rework.

 

[Kim B]

Is my last post visible or did I miss a repsonse?

Thank you

 

[dreid1011]

Okay, the following code is set to loop through all worksheets in the workbook looking for sheets with "by Store Data Pull " in the sheet name. For each sheet with that name, it will then loop through the desired range entering the formulas provided and changing the reference to the appropriate CSV tab. However, you didn't mention anything about the "Forecast x-xx" sheet name so I didn't do anything with it.

Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim frm1 As String, frm2 As String
Dim shNm1 As String, shNm2 As String

Application.ScreenUpdating = False
For Each ws In Worksheets
    If InStr(ws.Name, "by Store Data Pull") Then
        shNm1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        shNm2 = "'" & ws.Name & "'!"
        For i = 15 To 1256 Step 3
            j = i + 1
            frm1 = "=SUMIFS(" & shNm1 & "$D:$D," & shNm1 & "$A:$A," & shNm2 & "R4C," & shNm1 & "$B:$B," & shNm2 & "R[2]C1)"
            ws.Range(ws.Cells(4, i), ws.Cells(43, i)).Formula2 = frm1
            frm2 = "=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A," & shNm2 & "R4C,'Forecast 1-9'!$B:$B," & shNm2 & "R[2]C1)"
            ws.Range(ws.Cells(4, j), ws.Cells(43, j)).Formula2 = frm2
        Next i
    End If
Next
Application.ScreenUpdating = True

End Sub

 

[Kim B]

receiving an error on highlighted line

 

[dreid1011]

receiving an error on highlighted line

View attachment 108047

What is the error?

 

[Kim B]

Highlight row