Solved

EXCEL: USING A CELL VALUE TO REFERENCE A WORKBOOK IN A FORMULA

Posted on 2011-09-19
2
262 Views
Last Modified: 2012-05-12
Hi,

I have the following formula which perform a Vlookup in a workbook located in a different file:

=VLOOKUP(A3,'C:\Users\Roberto\Desktop\TEST\[TEST1.xls]Sheet1'!$A:$C,2,FALSE)

I would like that the name of the workbook [TEST1.xls] comes form Cell B2... so if I change the value there the Formula will link to a different Workbook...

It's that possible ?

Thank you in advance for your help,
Roberto.
0
Comment
Question by:Pabilio
2 Comments
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Hello,

you could use

=VLOOKUP(A3,INDIRECT("'"&B2&"Sheet1'!$A:$C"),2,FALSE)

but Indirect() will only work when the other file is open. A free add-in called Morefunc has a function called Indirect.Ext which also works with closed workbooks. You can download the add-in here: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

cheers, teylyn
0
 
LVL 5

Author Closing Comment

by:Pabilio
Comment Utility
Hi Teylyn!...

I totally forgot to close this question ... Sorry for the delay.

I tried the Indirect.Ext and it does the trick.

Thank you for your help.

Regards,
Roberto.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now