Hello,
I need to refer to a range of cells in a function. Same function is used in
different worksheets of same workbook. The name when used in a specific page
should refer to the cells from that page.
ie.,
I have defined a name as myname =a1:d5
when used in sheet sheet1, myname should give = 'sheet1'!a1:d5
when used in sheet sheet2, myname should give = 'sheet2'!a1:d5 like that...

Thanks

Function BJ()

dim oRng as range
Application.volatile

set orng=application.caller.parent.range("A1:D5")
.......
BJ= ...
end function


or if you want to use a name then

Myname=INDIRECT("A1:D5)

in both cases you would need to define the function as volatile because the
range is not referenced in the argument list for the function so Excel does
not know when to recalculate the function:
in general you should try to avoid using Application.volatile


--
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available

www.DecisionModels.com
"bj" wrote in message
...
Hello,
I need to refer to a range of cells in a function. Same function is used
in
different worksheets of same workbook. The name when used in a specific
page
should refer to the cells from that page.
ie.,
I have defined a name as myname =a1:d5
when used in sheet sheet1, myname should give = 'sheet1'!a1:d5
when used in sheet sheet2, myname should give = 'sheet2'!a1:d5 like
that...

Thanks