referencing a named range in another workbook

G

Guest

I get new files each season from our parent company. Often they have different names but the format and named ranges are always the same and have the same name. I have to link my workbook to cells in their workbook each time it's updated using an Index function. Does anyone know if there is a way I can reference a named range in one workbook1 by typing the path of workbook1 in a cell in workbook2 and having yet another cell look up that path to retrieve the indexed data from the workbook1? Currently it looks something like this:

=index('workbook1.xls!' rangeA,5,2)

What I'd like to do is something to this affect:

=index(b2 rangeA,5,2)

b2= cell with path of workbook1.xls
 
D

Dave Peterson

I think you'd want to use =indirect() to include that workbook name. And
=indirect() won't work if the other workbook is closed.

If the number of workbooks is limited, maybe you could include a worksheet
(Hidden???) that retrieves all the values and then use that to retrieve the
range you want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing named range from another workbook 5
Using Range().Formula with a Filename & Path 1
Hyperlink to another Workbook (97) 1
Find text in another workbook and paste if found match - VBA 1
How do I call up a line of code that references a cell/range in theactive workbook workbook where I 1
How do I set a combobox listfillrange to a range in another workb. 1
VBA Referencing a Named Cell Range in another Workbook 5
Formula copying from one workbook to another... 1

Top