Vlookup - File Names

  • Thread starter Thread starter Anita
  • Start date Start date
A

Anita

Does anyone know if I can use a variable in my vlookup
statement instead of the file name? I need to pull data
from many different files, one at a time, (same range
though), and would like to create a template so I can just
change the file name in one place without using
the "search and replace" function. Since the relevant file
name will be in each of the reports, it would be great if
I could use it in the formula.
 
Anita,

You would need to use INDIRECT for that, which unfortunately doesn't work with closed workbooks.

You could use the workbook's change event to do a search and replace for you, whenever you enter a value into a certain cell. For
example, if you put the old file name into cell A2, and then enter your new filename into cell A1, the code below will replace all
instances of the value in A2, including that in A2, so that any further changes in A1 will be reflected instantly.

The only caveat is that if some filenames have spaces and others don't, then you may get an error: workbook name/worksheet name
paths in formulas needs single quotes if they contain spaces, and switching back and forth won't be handled well by Excel.

HTH,
Bernie
Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Cells.Replace _
What:=Range("A2").Value, _
Replacement:=Range("A1").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Application.EnableEvents = False
End If
End Sub
 
Back
Top