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'