Dynamic Filename/Spreadsheet Reference within a formula

kogersdad

New member
Joined
May 2, 2012
Messages
17
Reaction score
0
Points
0
I have a spreadsheet named "current month" Data that I open and rename each month to the current month's data. So in 12 months I have 12 files: Jan Data, Feb Data, etc. I have a second set of monthly spreadsheets named Jan Sales Tax, Feb Sales Tax, etc.

There is a specific cell in the Jan Data spreadsheet that I need in the Jan Sales Tax spreadsheet and the same cell is needed from the Feb Data spreadsheet in the Feb Sales Tax spreadsheet and so on.

Here is the Formula in the Jan Sales Tax spreadsheet that I change every month to reflect the current month's need.
=+'[Jan Data.xlsx]Sheet1'!$A$7

Note: In Feb, I change "Jan" to "Feb", the remainder of the formula is not touched.

Is there any way to make the "Jan" portion of the formula dynamic so that when I enter the applicable month in cell A1 - Jan or Feb or March, etc, the dynamic portion of the formula will change to whatever is in cell A1


thanks in advance!!

 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
You can use the INDRECT & ADDRESS functions to do this. Try this:

Code:
=INDIRECT(ADDRESS(7,1,1,1,A1&" Data.xlsx"))

The source data sheets will need to be open.

Cheers,
 

kogersdad

New member
Joined
May 2, 2012
Messages
17
Reaction score
0
Points
0
Ok that seems to work perfectly with the exception that the source data has to be open. Is there a way to bypass this requirement? Or another method that doesn't have this requirement? Thanks a bunch already!!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
This VBA code and Function will pull data out of a closed workbook. Here is how it works

1. If you put Jan in A1 .... it will pull in the value found in A7 of closed Jan data.xlsx

2. it will put this value in cell A7 in your Open Jan tax file .


you can adjust the P variable to match the folder your file(s) reside in.




Code:
Sub TestGetValue2()
   MyMonth = Cells(1, 1).Value
    p = "c:\BizData"
    f = MyMonth & " data.xlsx"
    s = "Sheet1"
    
    
    Application.ScreenUpdating = False
    
    
    
    For R = 7 To 7
        For C = 1 To 1
            A = Cells(R, C).Address
            Cells(R, C) = GetValue(p, f, s, A)
        Next C
    Next R
    Application.ScreenUpdating = True

 
 

End Sub

paste this code right under the end sub of the above code ....

Code:
Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function
 
Top