Link problem with named range

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

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 wrote:
>
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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

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 wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >>
> >> 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

--

Dave Peterson

Point taken! (and good example)

Thanks again,

Jay


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 wrote:
>> >>
>> >> 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
>
> --
>
> Dave Peterson