referencing part of a named range

I have a two-dimensional named range (let's call it "test") and i want to reference various columns within the range, like:

= SUMPRODUCT ( column2 , column5 )

I know I could do this by creating two additional named ranges called "column2" and "column5" ... but is there a way to do it just using "test" and the numbers 2 and 5? maybe with an array over a lookup formula?

thanks,
pete

Look into the use of the OFFSET function. See attached screen print. I have a named range "test" that is B2:E6. I return the cell that is one cell down, one cell over, 1 cell high and 1 cell wide using the formula.

HTH

Jason

thanks for the suggestion -- i've been playing with the offset function and it does work to use multi-cell offset formulas in formulas (like sumproduct) that take arrays.

there are two problems with the offset formula, though:

- i need to know the height (# rows) in the named range to capture an entire column, and would have to manually edit the formula if this changed

- i don't think it could be incorporated into conditional formulas (like sumif)

...that's why i was thinking something involving a lookup function might be the answer. but maybe there's some way to just reference a defined subset of a range in the same way you'd reference the range itself?

wait, i take back the first one -- you can just omit the height and it will be the same height as the range -- like

=offset(test,1,0,,1)

would return the entire second column of "test"