Is there a way to set a named range, based on the active sheet?

Example:
Range1 =LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000))

I want to be able to set this named ranged, based on the sheet I am in. If
on sheet 2 it would set the range as:
Range1 =LOOKUP(2,1/('Sheet2'!$B$1:$B$5000<""),ROW('Sheet2'!$B$1:$B$5 000))

etc...

There are 2 types of named ranges. Global and Local. Global named ranges
apply to the entire wokrbook and can be referenced form any sheet. Local
named ranges are defined on one sheet and they apply only to that sheet.

Local named ranges are created by appending the sheet name at the start of
the named range. So something like this

Named range = 'Sheet1'!MyStuff
Reference = 'Sheet1'!$B$1:$B$5000
you refer to it using MyStuff on sheet 1

Named range = 'Sheet2'!MyStuff
Reference = 'Sheet2'!$B$1:$B$5555
you refer to it using MyStuff on sheet 2
--
HTH...

Jim Thomlinson


"Josh O." wrote:

Is there a way to set a named range, based on the active sheet?

Example:
Range1 =LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000))

I want to be able to set this named ranged, based on the sheet I am in. If
on sheet 2 it would set the range as:
Range1 =LOOKUP(2,1/('Sheet2'!$B$1:$B$5000<""),ROW('Sheet2'!$B$1:$B$5 000))

etc...