VLOOKUP Formula with "dynamic" source file

[orit]

I would like to build a vlookup that its source data (table array source file) will be dynamic (the sheet name and range aren't dynamic).
I want to enter on one cell the path where the file is located, in second cell the constant part of the file name and in third cell a number - that will be updated and will reflect the updated file revision. for example:
I would like to build the following vlookup:

=VLOOKUP($D3,'D:\Projects\Templates\[csdm_PLM_v21.xls]Relevant'!$A$4:$AA$202,3,0)

I want to place the path (D:\Projects\Templates\) on one cell, the first part of the file name (csdm_PLM_v) on the 2nd cell and the ver number (21) in a 3rd cell. the ver number will be changed frequently and I want the function to be dynamic.
If my worksheet looks like:
Path | filePrefix | Version
--------------------------------
D:\Projects\Templates\ | csdm_PLM_v | 21
How can I make the following vlookup dynamic:

=VLOOKUP($D3,'D:\Projects\Templates\[csdm_PLM_v21.xls]Relevant'!$A$4:$AA$202,3,0)

Thanks

 

[stanleydgromjr]

You will need to create a dynamic formula with a macro

You will need to create a dynamic formula with a macro.

Try this in a test spreadsheet.

Enter the following information into cells A1:C2

orit - VLOOKUP Formula with dynamic source file.xls
	A	B	C	D
1	Path	filePrefix	Version
2	D:\Projects\Templates\	csdm_PLM_v	21
Sheet1

Press ALT and F11

Insert a Module

Copy the following code:

Option Explicit


Sub CreateDynamicFormula()
'
' CreateDynamicFormula Macro
' Macro created 10/16/2006 by Stanley D. Grom, Jr.
'

    Dim strPath As String
    Dim strFilePrefix As String
    Dim strFileVersion As String
    
    Range("A2").Select
    strPath = ActiveCell.Value
    'MsgBox "strPath = " & strPath
    
    Range("B2").Select
    strFilePrefix = ActiveCell.Value
    'MsgBox "strFilePrefix = " & strFilePrefix
    
    Range("C2").Select
    strFileVersion = ActiveCell.Value
    'MsgBox "strFileVersion = " & strFileVersion

    Range("A4").Select

    ActiveCell.Value = "=VLOOKUP($D3,'" & strPath & "[" & strFilePrefix & strFileVersion & ".xls" & "]Relevant'!$A$4:$AA$202,3,0)"


    'When you are satisfied with the macro, add an apostrophe
    '  in front of the following line of code:
    Exit Sub
    
    
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False

'
End Sub

And paste the code in the new Module.


Run the macro 'CreateDynamicFormula'.

'When you are satisfied with the macro, add an apostrophe
' in front of the following line of code:
Exit Sub

I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan

 

[orit]

Thanks a lot.

When I run the macro, I get the message:
Message title: Microsoft Visual Basic
Message body: 400

What can be the problem?

Thanks