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!!!

=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!!!

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!!!

As long as the range has contiguous entries, here's a non-volatile approach:

=Sheet2!J4:INDEX(Sheet2!J4:J100,COUNTA(Sheet2!J4:J 100))

Adjust the end of range as needed. For example, if you will never need to go
beyond J100 then use J100. If you will never need to go beyond J50 then use
J50.


--
Biff
Microsoft Excel MVP


"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!!!