NamedRange - Variable Sheet
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... |
NamedRange - Variable Sheet
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... |
All times are GMT +1. The time now is 01:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com