Named ranges
Posted by Jaime de la Isla on June 28, 2001 10:24 AM
Is there a way to assign multiple ranges to the
same name and toggle between them?
Example: When month = 1 thru 6, name = A1:D10 in sheet 1
When month = 7 thru 12, name = A1:d10 in sheet 2
Posted by Aladin Akyurek on June 28, 2001 10:40 AM
Jaime
Here is one way.
=VLOOKUP(MONTH(A1),{1,"Sheet1!A1:A10";6,"Sheet2!D1:D10"},2,1)
where an array constant is used as a lookup table. You can expand this table at will [ If you need more help on this concept, just ask. ]
And name the cell of the formula via the Name Box.
Lets say it's named as ThisSheet.
You need to invoke INDIRECT(ThisSheet) in formulas in order to access the range ThisSheet really refers to.
Aladin
Posted by lenze on June 28, 2001 10:59 AM
Yes; First name the two ranges individually (Ex Rng1 & Rng2). Then choose Insert>Name>Define and create a range name (Ex YourRange). Then in the refers to field enter =IF(Month<7,Rng1,Rng2) where Month is the name of the range where the month value resides. Now to toggle based on the month value, chosed Edit>Go To>and enter YourRange in the reference box. You can also use a hyperlink to YourRange or a command button.
