Link problem with named range

[Jay Northrop]

Hello,

Workbook1 has a vertical column of 5 cells (with values of 1, 2, 3, 4, 5)
named NUMBRS. Workbook2 is linked to Workbook1 (at least I think it is!!),
and has the formula SUM(NUMBRS). However, the formula results in zero.
Apparently, Workbook2 does not recognize the range name NUMBRS in Workbook1.
Is there a reason for this? Your help is appreciated.

Thanks,

Jay

 

[Dave Peterson]

If you write your formula like:

=SUM(book1.xls!numbrs)

does it work?

I'd open both workbooks and start the formula =sum(

then point at that other range and let excel get the syntax correct.

Another option is to define a name within workbook2 that refers to the range in
workbook1.

With workbook2 active:
insert|name|define
give it a nice name in the names in workbook box (numbrs1 ???)
click on the refers to box
and use Window (on the menu bar) to go to the other workbook.
Select the worksheet you need
Point at the range.

Then this defined name exists in workbook2, but points at workbook1.
=sum(numbrs1)

would work ok, too.

 

[Jay Northrop]

Dave,

I tried both of your suggestions, and they both work fine. Originally, I
thought that, if a workbook was already linked, it would automatically
recognize the range names defined in the original workbook. Apparently not!

Thanks, Dave, for your help!

Jay

 

[Dave Peterson]

Ahh, but that would be some kind of program that could do that--imagine having a
dozen workbooks open and each of them could have a range with the same name.

Kind of like yelling "hey, Jay" at the mall. Lots of heads will turn.

But if I yell "hey, Jay Northrup", only one (or two <vbg>) should turn.

 

[Jay Northrop]

Point taken! (and good example)

Thanks again,

Jay