Variable in an external link?

[vettetax]

=INDEX('F:\Financials-MonthlyData\2008\Fiserv 2008 Monthly Financials\MPC Supp Workbook\[May 2008 MPC Supp Workbook.xls]Forecast'!$B$19:$M$19,1,$G$1)

In this formula, is there a way to make "May" a variable? By making use of a vlookup perhaps? So if G1 is 5, I want the link to point to the May file, if 6, the June file, if 7, the July file, etc.

 

[DonkeyOte]

I *think* you could use a custom function to do this given you're working across workbooks (standard) INDIRECT not likely to be best option

Function rng_x(mth As Integer)
Dim str As String
str = "F:\Financials-MonthlyData\2008\Fiserv 2008 Monthly Financials\MPC Supp Workbook\[ZZZ 2008 MPC Supp Workbook.xls]Forecast'!$B$19:$M$19"
str = Replace(str, "ZZZ", Format(DateSerial(1900, mth, 1), "Mmm"))
rng_x = Range(str)
End Function

With formula then reading

=INDEX(rng_x($G$1),1,$G$1)

could be extended further to incorporate multiple years - ie use a date to refine the path -- changing 2008 etc in addition to month.

Hope that helps.

Should have added: untested...

 

[vettetax]

Two Q's:

I pasted the code to a module, but I'm getting a #VALUE! error

Two, if it matters, I need the code to reference the full name of the month not just Mmm.

 

[DonkeyOte]

Right click on the Excel icon to the right of File menu - select View Code

on resulting screen from Insert menu select Module

Paste code into the new module.

Save the file.

Check that your Macros Security is not set to High (would need to be Medium (or lower but not recommended))

If was set to High you must close out of Excel and re-open the file & when prompted choose Enable Macros.

You should now find that the NAME error has disappeared -- hopefully replaced by the correct value.

If you need to full name of month change

str = Replace(str, "ZZZ", Format(DateSerial(1900, mth, 1), "mmm"))

to

str = Replace(str, "ZZZ", Format(DateSerial(1900, mth, 1), "mmmm"))

If you think this would be a regular variable let me know and we can alter the function and add a variable accordingly.

Let's see if it works first -- I will try and test myself.

 

[vettetax]

Macro settings set to allow all macros to run.

Thanks, but am still getting #VALUE!

 

[DonkeyOte]

Yes, sorry, my example won't work as it was for internal ranges -- please disregard.

Am still playing with this but at the moment I can't get it to work...

Maybe one of the other guys can help out with this...

 

[DonkeyOte]

I couldn't get this to work trying to parse the variant string directly into INDEX however I did think of a way around it based on updating G1 (month number)

You could put some code in G1 such that if you double click on that cell it will ask end-user to enter month number, (see below) -- this could should reside in the worksheet on which the G1 value resides -- to do this activate the sheet in Excel that holds G1 and right click on the tab name and select view code, enter the below into that code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim I1 As Variant
If Target.Address = "$G$1" Then
1
    I1 = InputBox("Enter Month Number (1-12)", "Month No.", 1)
    Select Case IsNumeric(I1)
        Case True
            If I1 < 0 Or I1 > 12 Then GoTo 1
        Case False
            GoTo 1
    End Select
    Target.Value = I1
    Cancel = True
    Call REVISE_NAMES(Int(I1))
End If
End Sub

Sub REVISE_NAMES(mx As Integer)
Dim str As Variant
str = "='C:\[ZZZ08.xls]Forecast'!$B$19:$M$19"
str = Replace(str, "ZZZ", Format(DateSerial(2000, mx, 1), "mmm"))
ActiveWorkbook.Names.Add Name:="test", RefersTo:=str
End Sub

Now in theory what this will do is keep a given named range (test) up to date referring to the appropriate external file pending month value....

Once the month has been updated then you could use INDEX as normal but use test as range, eg:

=INDEX(test,1,1)

Obviously the above was based on using sample files C:\Apr08.xls and C:\May08.xls

If this is something you think you would want to make use of let me know and we can look at altering to meet your own specific requirements.

However, I remain convinced there must be a simpler way of doing this (avoiding INDIRECT) so look forward to others' posts.

 

[vettetax]

AWESOME!!! That did the trick!!! Cool feature.