Update file path based on cell value

Question

I'm trying to copy and paste a formula into a certain range of cells. The formula I have (shown below) needs to update based on the contents of cells from a source workbook. Some context, the excel file where the macro below resides is opened by a macro from the source workbook (where the source cell contents are).

The source cells would be updating the 'May2017' in this case.

Sub PullPUProj()

Range("D18:D104").Formula = "=IFERROR(OFFSET('[Athens_OperatingProjection_May2017.xlsx]Budget Detail'!$A$7,MATCH($A16,'[Athens_OperatingProjection_May2017.xlsx]Budget Detail'!$A$8:$A$284,0),MATCH('[Athens_OperatingProjection_May2017.xlsx]Budget Detail'!$BK$7,'[Athens_OperatingProjection_May2017.xlsx]Budget Detail'!$B$7:$CP$7,0)),0)"

End Sub

Any help or references would be much appreciated!!

Answer 1

Let's say cell A1 has the text you want to use in place of "May2017". You can just split the formula by closing, "" and adding & [variable] & " to reopen it.

Range("D18:D104").Formula = "=IFERROR(OFFSET('[Athens_OperatingProjection_" & A1 & ".xlsx]Budget Detail'!$A$7,MATCH($A16,'[Athens_OperatingProjection_" & A1 & ".xlsx]Budget Detail'!$A$8:$A$284,0),MATCH('[Athens_OperatingProjection_" & A1 & ".xlsx]Budget Detail'!$BK$7,'[Athens_OperatingProjection_" & A1 & ".xlsx]Budget Detail'!$B$7:$CP$7,0)),0)"

Note: You may need to make the A1 reference absolute if you want to drag that formula around but keep the reference to A1, (so $A$1).

Answer 2

Assuming cell A1 has the string, then you may try this...

Range("D18:D104").Formula = "=IFERROR(OFFSET('[Athens_OperatingProjection_" & Range("A1").Value & ".xlsx]Budget Detail'!$A$7,MATCH($A16,'[Athens_OperatingProjection_" & Range("A1").Value & ".xlsx]Budget Detail'!$A$8:$A$284,0),MATCH('[Athens_OperatingProjection_" & Range("A1").Value & ".xlsx]Budget Detail'!$BK$7,'[Athens_OperatingProjection_" & Range("A1").Value & ".xlsx]Budget Detail'!$B$7:$CP$7,0)),0)"

Answer 3

Similar to how others have suggested, but two changes. 1) Instead of referencing the Cell directly in the formula, define what you want outside of it. That way if you ever decide to move it from say Cell A1, you only have to update one part. 2) Also include the sheet name as well. If the macro can be executed from other sheets, it will likely produce an unexpected error because it won't find what it needs.

Month = Sheets("Budget Detail").Range("A1")

Range("D18:D104").Formula = "=IFERROR(OFFSET('[Athens_OperatingProjection_" & Month & ".xlsx]Budget Detail'!$A$7,MATCH($A16,'[Athens_OperatingProjection_" & Month & ".xlsx]Budget Detail'!$A$8:$A$284,0),MATCH('[Athens_OperatingProjection_" & Month & ".xlsx]Budget Detail'!$BK$7,'[Athens_OperatingProjection_" & Month & ".xlsx]Budget Detail'!$B$7:$CP$7,0)),0)"