hi all

how do i reference a named range from another book?

just a generic example would be sufficient
ie if my named range is

'MyNamedRange' and it is defined in the workbook 'Book2.xls' on worksheet
MySheetName in path c:\temp

how do i reference MyNamedRange in 'Book3.xls'

Dim bk as Workbook
Dim rng as Range
Dim bNotOpen as Boolean
on Error Resume Next
set bk = Workbooks("Book2.xls")
On Error goto 0
if bk is nothing then
set bk = Workbooks.Open("C:\temp\book2.xls")
bNotOpen = True
End if
set rng = bk.Names("MyNamedRange").RefersToRange
v = rng.Value
if bNotOpen then
bk.Close SaveChanges:=False
set rng = nothing
set bk = nothing
End if


--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
hi all

how do i reference a named range from another book?

just a generic example would be sufficient
ie if my named range is

'MyNamedRange' and it is defined in the workbook 'Book2.xls' on worksheet
MySheetName in path c:\temp

how do i reference MyNamedRange in 'Book3.xls'

thanks Tom! that's perfect, however i should have specified...
i need this in a cell formula version

the formula i am using is a sumproduct formula which looks like

=sumproduct((MyNamedRange1="A")*(MyNamedRange2))
(as an array formula)

i am copying this sheet to another workbook and need to reference it from
the new workbook

ie if i am referencing a cell from another workbook i do
=[Book2.xls]MySheet!$E$44

i need to do the same thing, but reference the Named Range instead

that a bit clearer?


thanksi!

J
"Tom Ogilvy" wrote:

Dim bk as Workbook
Dim rng as Range
Dim bNotOpen as Boolean
on Error Resume Next
set bk = Workbooks("Book2.xls")
On Error goto 0
if bk is nothing then
set bk = Workbooks.Open("C:\temp\book2.xls")
bNotOpen = True
End if
set rng = bk.Names("MyNamedRange").RefersToRange
v = rng.Value
if bNotOpen then
bk.Close SaveChanges:=False
set rng = nothing
set bk = nothing
End if


--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
hi all

how do i reference a named range from another book?

just a generic example would be sufficient
ie if my named range is

'MyNamedRange' and it is defined in the workbook 'Book2.xls' on worksheet
MySheetName in path c:\temp

how do i reference MyNamedRange in 'Book3.xls'

Thanks Tom!

last question - does your method work if the named range is created
dynamically?
ie =offset(......)?

"Tom Ogilvy" wrote:

The easiest way is to open book2. Then build your formula in the formula
bar by putting in

=SumProduct((
then navigate to book2 and click on the cell(s) that make up MyNamedRange

when Excel updates the formula it will use the named range in the formula
and you will have the syntax.

--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
thanks Tom! that's perfect, however i should have specified...
i need this in a cell formula version

the formula i am using is a sumproduct formula which looks like

=sumproduct((MyNamedRange1="A")*(MyNamedRange2))
(as an array formula)

i am copying this sheet to another workbook and need to reference it from
the new workbook

ie if i am referencing a cell from another workbook i do
=[Book2.xls]MySheet!$E$44

i need to do the same thing, but reference the Named Range instead

that a bit clearer?


thanksi!

J
"Tom Ogilvy" wrote:

Dim bk as Workbook
Dim rng as Range
Dim bNotOpen as Boolean
on Error Resume Next
set bk = Workbooks("Book2.xls")
On Error goto 0
if bk is nothing then
set bk = Workbooks.Open("C:\temp\book2.xls")
bNotOpen = True
End if
set rng = bk.Names("MyNamedRange").RefersToRange
v = rng.Value
if bNotOpen then
bk.Close SaveChanges:=False
set rng = nothing
set bk = nothing
End if


--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
hi all

how do i reference a named range from another book?

just a generic example would be sufficient
ie if my named range is

'MyNamedRange' and it is defined in the workbook 'Book2.xls' on
worksheet
MySheetName in path c:\temp

how do i reference MyNamedRange in 'Book3.xls'

I don't know, but if it doesn't, create a named range in your Book2 that
isn't dynamic and do it. That will give you the syntax, then edit it use
your dynamic range.

If you intend to have book2 closed after creating the link, I am not sure if
that works or not. You can tell us.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" wrote in message
...
Thanks Tom!

last question - does your method work if the named range is created
dynamically?
ie =offset(......)?

"Tom Ogilvy" wrote:

The easiest way is to open book2. Then build your formula in the
formula
bar by putting in

=SumProduct((
then navigate to book2 and click on the cell(s) that make up
MyNamedRange

when Excel updates the formula it will use the named range in the
formula
and you will have the syntax.

--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
thanks Tom! that's perfect, however i should have specified...
i need this in a cell formula version

the formula i am using is a sumproduct formula which looks like

=sumproduct((MyNamedRange1="A")*(MyNamedRange2))
(as an array formula)

i am copying this sheet to another workbook and need to reference it
from
the new workbook

ie if i am referencing a cell from another workbook i do
=[Book2.xls]MySheet!$E$44

i need to do the same thing, but reference the Named Range instead

that a bit clearer?


thanksi!

J
"Tom Ogilvy" wrote:

Dim bk as Workbook
Dim rng as Range
Dim bNotOpen as Boolean
on Error Resume Next
set bk = Workbooks("Book2.xls")
On Error goto 0
if bk is nothing then
set bk = Workbooks.Open("C:\temp\book2.xls")
bNotOpen = True
End if
set rng = bk.Names("MyNamedRange").RefersToRange
v = rng.Value
if bNotOpen then
bk.Close SaveChanges:=False
set rng = nothing
set bk = nothing
End if


--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
hi all

how do i reference a named range from another book?

just a generic example would be sufficient
ie if my named range is

'MyNamedRange' and it is defined in the workbook 'Book2.xls' on
worksheet
MySheetName in path c:\temp

how do i reference MyNamedRange in 'Book3.xls'

this worked when the originating book (MyBook.xls) was open:

'c:\temp\MyBook.xls'!MyNamedRange
when you close MyBook, there is a #REF! error
(just leaving the MyNamedRange w/o the path produces a #VALUE! error)

anyone have an idea on how to get this to work when the original book is
closed?

i'm thinking this will work if the named range is static, but i need to use
dynamic ranges


"Tom Ogilvy" wrote:

I don't know, but if it doesn't, create a named range in your Book2 that
isn't dynamic and do it. That will give you the syntax, then edit it use
your dynamic range.

If you intend to have book2 closed after creating the link, I am not sure if
that works or not. You can tell us.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" wrote in message
...
Thanks Tom!

last question - does your method work if the named range is created
dynamically?
ie =offset(......)?

"Tom Ogilvy" wrote:

The easiest way is to open book2. Then build your formula in the
formula
bar by putting in

=SumProduct((
then navigate to book2 and click on the cell(s) that make up
MyNamedRange

when Excel updates the formula it will use the named range in the
formula
and you will have the syntax.

--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
thanks Tom! that's perfect, however i should have specified...
i need this in a cell formula version

the formula i am using is a sumproduct formula which looks like

=sumproduct((MyNamedRange1="A")*(MyNamedRange2))
(as an array formula)

i am copying this sheet to another workbook and need to reference it
from
the new workbook

ie if i am referencing a cell from another workbook i do
=[Book2.xls]MySheet!$E$44

i need to do the same thing, but reference the Named Range instead

that a bit clearer?


thanksi!

J
"Tom Ogilvy" wrote:

Dim bk as Workbook
Dim rng as Range
Dim bNotOpen as Boolean
on Error Resume Next
set bk = Workbooks("Book2.xls")
On Error goto 0
if bk is nothing then
set bk = Workbooks.Open("C:\temp\book2.xls")
bNotOpen = True
End if
set rng = bk.Names("MyNamedRange").RefersToRange
v = rng.Value
if bNotOpen then
bk.Close SaveChanges:=False
set rng = nothing
set bk = nothing
End if


--
Regards,
Tom Ogilvy


"Gixxer_J_97" wrote in message
...
hi all

how do i reference a named range from another book?

just a generic example would be sufficient
ie if my named range is

'MyNamedRange' and it is defined in the workbook 'Book2.xls' on
worksheet
MySheetName in path c:\temp

how do i reference MyNamedRange in 'Book3.xls'