Results 1 to 4 of 4

Thread: Dynamic Filename/Spreadsheet Reference within a formula

  1. #1

    Dynamic Filename/Spreadsheet Reference within a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!!

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    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,

  3. #3
    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!!

  4. #4
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •