referencing a named range in another workbook

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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.
 
Back
Top