Dynamic Defined name formula

[Jim X]

I need an offset type formula based on the cell value of A1

If A1= 3 then I need the name to return this range if A22 is the active
cell

$A$22,$B$21:$B$22,$C$20:$C$22

If A1= 5 then I need:

$A$22,$B$21:$B$22,$C$20:$C$22,$D$19:$D$22,$E$18:$E$22

I built something utilizing VBA code to do this, but would prefer a formula
because I am extremely anal and neurotic


=Offset(A22,0,0,what goes here????)

using xl 2003

 

[bobocat]

to make it simple, name $A$22,$B$21:$B$22,$C$20:$C$22 as Range1
and $A$22,$B$21:$B$22,$C$20:$C$22,$D$19:$D$22,$E$18:$E$22 as Range 2

Define another name "Drange"
Refers to :
=if(sheet1!$A$1=1, range1, if(sheet!$a$1=5, range2))