referencing a name from another book

[Guest]

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'

 

[Tom Ogilvy]

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

 

[Guest]

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]

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

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

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

 

[Guest]

Thanks Tom!

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

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

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

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


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'

 

[Tom Ogilvy]

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

Thanks Tom!

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

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

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
:

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


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'