You should look up the OFFSET function in Help. This enables you to
define a dynamic (i.e. varying) range relative to a base range, using
variable values. It is reasonably easy to use OFFSET to refer to a
subset of a given named range. If you would like to see how it is
done, send me your spreadsheet, via Google groups, or upload it in
excelforum and I will show you.

Example: =SUM(OFFSET(YR2005,0,2,1,3)) will calculate the sum of cells
3 through 5 of the first row of a range called YR2005 (referring to the
first cell as "cell 1"). We are defining a new range "offset" from the
top left hand corner of YR2005 (the first parameter) by 0 rows (the
second parameter) and 2 columns (the third parameter) with a depth of 1
row and a width of 3 rows (third and fourth parameters).