VLOOKUP Formula with "dynamic" source file

orit

New Member
Joined
Oct 31, 2004
Messages
44
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:
Code:
=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:
Code:
=VLOOKUP($D3,'D:\Projects\Templates\[csdm_PLM_v21.xls]Relevant'!$A$4:$AA$202,3,0)

Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Joined
Jul 30, 2006
Messages
3,656
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
ABCD
1PathfilePrefixVersion
2D:\Projects\Templates\csdm_PLM_v21
Sheet1



Press ALT and F11

Insert a Module

Copy the following code:

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

New Member
Joined
Oct 31, 2004
Messages
44
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
 

Forum statistics

Threads
1,144,520
Messages
5,724,827
Members
422,581
Latest member
samiak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top