Vlookup file location changes depending on value within cell

[masterbaker]

Here's my issue:

I have a vlookup formula as follows:
VLOOKUP(N17,'C:\Documents and Settings\mstege\Desktop\Paymentech
Temp\[052406.xls]052406'!$A$2:$Z$29,26,FALSE)

My issue is I want the file named 052406.xls (a date) to change depending on
the value of a specific cell (G12 - which changes by date) without having the
file named 052406 open.

G12 is:
=CONCATENATE(C12,E12,F12)
which yields the value 052406

How do I get the Vlookup to change the file it's looking in by this cell
value G12?

Essentially I want:
VLOOKUP(N17,'C:\Documents and Settings\mstege\Desktop\Paymentech
Temp\[G12.xls]052406'!$A$2:$Z$29,26,FALSE)

I have tried the Indirect function to no avail!!! Please help.

 

[vane0326]

Hope this helps.

1.) Enter date in cell G12

( 052406 )

2.) Enter your formula in cell K12 without the equal sign, I substitute
your file name with "date"

VLOOKUP(N17,'C:\Documents and
Settings\mstege\Desktop\PaymentechTemp\[date.xls]date'!$A$2:$Z$29,26,FALSE)

3.) Enter this formula in cell M12

="="&(SUBSTITUTE(K12,"date",$G12))


4.) Copy this code and right click on your worksheet tab and paste it
onto the white area and close it.


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G12")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Range("I12").Value = Range("M12").Value

End Sub
--------------------



Now change the date in cell G12 you wil get your result. It will place
your formula in cell I12.