Referencing a table outside of the active worksheet.

[amiersen]

Does someone know a way to do this?:

I want a user to be able to input an excel worksheet path into a textbox (including the table name they will be using), then I want my program to use that textbox value to find the specified worksheet/table and use it in a vlookup formula.

I've been trying to write the path to a worksheet cell and then use that cell in a vlookup formula, but so far I haven't had any success.

Thanks in advance!

 

[just_jon]

Welcome to MrEcxel -- care to give a more concrete example?

Path you're feeding it, the code you've got for looking up, etc.

 

[amiersen]

I have a textbox for someone to type a path in, ex. Z:\Excel\Premium Calculations.xls

I then write the value of the textbox to a cell in a worksheet using VB code
ex. Activecell.offset (1,3) = txtbox1.value

I then want my program to use the path in the cell (Z:\Excel\Premium Calculations.xls) to access a table at that location and find an amount using the vlookup formula
ex. =Vlookup (18, $D$2, 1)

 

[just_jon]

I have a textbox for someone to type a path in, ex. Z:\Excel\Premium Calculations.xls

I then write the value of the textbox to a cell in a worksheet using VB code
ex. Activecell.offset (1,3) = txtbox1.value

I then want my program to use the path in the cell (Z:\Excel\Premium Calculations.xls) to access a table at that location and find an amount using the vlookup formula
ex. =Vlookup (18, $D$2, 1)

You're looking for INDIRECT --

=VLOOKUP(18,INDIRECT(D2),1)

NOTE - You need also to specify a range name in your file/path string, sheet also [ well, usually ].

The file referenced in D2 MUST be open, or INDIRECT will return #REF!

 

[amiersen]

Thank you very much, my problem is resolved.