Linking to named ranges in another workbook

[Guest]

Someone told me how to do this put I have misplaced my notes:

Workbook A is a consolidation workbook that links to named ranges in
"source" Workbooks B, C, D, E....etc. There is a way to display all the named
ranges in the source workbooks and to select them by pointing, so as to
reduce the chance of formula typos. I just don't remember how. Help,
please!!!! and Thanks!

 

[Guest]

WorkBookName!RangeName

For instance if you wanted to sum the range named DataRange in a workbook
named MyData in your current workbook the formula would look like this:

=SUM(MyData.xls!DataRange)

 

[Guest]

Thanks. What I was really looking for is a way to make appear a dialog box
containing all the named ranges in the source workbook so that I could select
the named ranges from the list instead of typing them from scratch. Someone
had told me how to make the list of named ranges appear; I just forgot how.

 

[Bruce Sinclair]

Thanks. What I was really looking for is a way to make appear a dialog box
containing all the named ranges in the source workbook so that I could select
the named ranges from the list instead of typing them from scratch. Someone
had told me how to make the list of named ranges appear; I just forgot how.

IIRC you just click on the little arrow next to the name box on the relevant
tool bar ... and the list will drop down and allow you to select from it.

HTH

Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Guest]

That didn't work for me but, fortuitously, I stumbled upon the method that I
used before but had forgotten about (and it was in Excel Help no less!):

Open both books and arrange them on horizontal or vertical windows; start by
typing = on the cell that will receive data from the other workbook; move the
cursor to the source workbook and click F3, which will open a window titled
"Paste Names" with the names of all available named ranges. You can select a
name with the mouse, and click OK, thus avoiding the risky exercise of typing
the range name from scratch.