Question relating to Named Ranges which exist in another workbook.

[Guest]

I am wondering if someone can answer the following question I have.

I have a workbook called Master Lookup which has one sheet called Master in
it which has several columns.

Each column is a named range (i.e. Establishment, Gender, Area….).

What I need to do is to reference any of the named ranges from another
workbook, and I am firstly not sure whether Excel 2002 allows reference from
1 workbook to another. In most situations, workbook call Master Lookup will
not be ‘open’ when other workbooks reference any of the named ranged.

I am aware that if I want to use a named range as part of data validation
that is in the same workbook, I would enter =sheetname through data
validation in the appropriate cell, however if it is possible to reference
another workbook, how would I enter the reference to that workbook.

Additionally, as most of the named ranges will be variable in length I
intend to have them set up using the OFFSET command.

Any help offered would be appreciated.

 

[Guest]

This is a good question.

In a workbook called helper.xls we define a range A18 and give it the Name
zz

In a different workbook we can enter:

=SUM(helper.xls!zz)

Just be sure that helper.xls is open so the reference cal be resolved the
the second workbook.

 

[Guest]

Gary,

Thanks for your input. I have subsequently found that Debra Dalgleish
(http://www.contextures.on.ca/tiptech.html) has great examples relating to
exactly what I want.

I am in the process of trying out her step-by-step instructions to resolve
my question.

Once again thank U for your time, much appreciated.
Pank