How to create a 'name' which is not worksheet spcific

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bj - 24 Jul 2006 08:22 GMT

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

Reply to this Message

Charles Williams - 24 Jul 2006 10:28 GMT

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

Signature

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available

www.DecisionModels.com

> Hello,
> I need to refer to a range of cells in a function. Same function is used
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message