Thanks John!

Works like a charm.

"John C" wrote:

=OFFSET(Sheet2!$J$4,0,0,counta,1)
Understand, that by itself will error, (unless you enter it as an array),
but otherwise, you stick that into your formula, and it will define itself as
a 1 column wide array starting at Sheet2!$J$4 and going down how ever many
rows is tabulated by your said COUNTA formula
Also note: This is a volatile function, which means it 'constantly
calculates'. If you open this file and then close it, and do nothing, it will
ask if you want to save changes.
--
** John C **

"Inov8desin" wrote:

I have a constantly changing data set and want to use the range in formulas.
For example, one week I will use sheet2!J4:J20, the next week would be
J4:J50, then J4:J10, etc.

The beginning cell is always the same and I can find the last cell using a
counta function on the list.

I have tried the address function and can get the cell returned, but cannot
figure out how to use the information in another function, such as vlookup,
sum, etc.

HELP!!!