PDA

View Full Version : Dynamic Filename/Spreadsheet Reference within a formula



kogersdad
2012-07-15, 03:12 PM
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
2012-07-15, 09:15 PM
You can use the INDRECT & ADDRESS functions to do this. Try this:



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


The source data sheets will need to be open.

Cheers,

kogersdad
2012-07-16, 05:24 PM
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
2012-07-17, 01:02 AM
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.





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


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