Reference a changing range
 

Hi there,

I am trying to reference a range that changes as each day goes by.
Currently its A2:A500....Tomorrow it will be A2:A501 etc etc.

So I am trying something like this but its not working A2:Count(A2:A10000).
I think i might need to use offset but not sure.

Thanks a lot

SD

Reference a changing range
 

I'm going to assume that there are no blank rows in column A until the last
entry and that A1 has an entry.

Try
=offset(A1,1,0,counta(A:A)-1,1)

"SD" wrote:

Hi there,

I am trying to reference a range that changes as each day goes by.
Currently its A2:A500....Tomorrow it will be A2:A501 etc etc.

So I am trying something like this but its not working A2:Count(A2:A10000).
I think i might need to use offset but not sure.

Thanks a lot

SD

Reference a changing range
 

See if this helps:

http://contextures.com/xlNames01.html#Dynamic

Biff

"SD" wrote in message
...
Hi there,

I am trying to reference a range that changes as each day goes by.
Currently its A2:A500....Tomorrow it will be A2:A501 etc etc.

So I am trying something like this but its not working
A2:Count(A2:A10000).
I think i might need to use offset but not sure.

Thanks a lot

SD

Reference a changing range
 

Thanks for that but not sure if its working...I want to do a linest function
on the range from a2 to last value in column A as y. So i wanted to
reference A2:A500 (if A500 is last cell with any values). Not sure if this
forumal works? Thanks.

"Barb Reinhardt" wrote:

I'm going to assume that there are no blank rows in column A until the last
entry and that A1 has an entry.

Try
=offset(A1,1,0,counta(A:A)-1,1)

"SD" wrote:

Hi there,

I am trying to reference a range that changes as each day goes by.
Currently its A2:A500....Tomorrow it will be A2:A501 etc etc.

So I am trying something like this but its not working A2:Count(A2:A10000).
I think i might need to use offset but not sure.

Thanks a lot

SD

Reference a changing range
 

It should do. I think Barb assumed there was a heading in A1.

You would use that formula inside another function, like so

=SUM(OFFSET(A1,1,0,COUNTA(A:A)-1,1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"SD" wrote in message
...
Thanks for that but not sure if its working...I want to do a linest
function
on the range from a2 to last value in column A as y. So i wanted to
reference A2:A500 (if A500 is last cell with any values). Not sure if
this
forumal works? Thanks.

"Barb Reinhardt" wrote:

I'm going to assume that there are no blank rows in column A until the
last
entry and that A1 has an entry.

Try
=offset(A1,1,0,counta(A:A)-1,1)

"SD" wrote:

Hi there,

I am trying to reference a range that changes as each day goes by.
Currently its A2:A500....Tomorrow it will be A2:A501 etc etc.

So I am trying something like this but its not working
A2:Count(A2:A10000).
I think i might need to use offset but not sure.

Thanks a lot

SD

Reference a changing range
 

Do you know how I would enter a range reference that would subtract col b
from col a (I am in col C) but the rows would change each time I rerun the
reporrt. I'm trying to write a macro to automate everything and I'm ok if my
data is static...

I'm feeling very lost

Thanks

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlNames01.html#Dynamic

Biff

"SD" wrote in message
...
Hi there,

I am trying to reference a range that changes as each day goes by.
Currently its A2:A500....Tomorrow it will be A2:A501 etc etc.

So I am trying something like this but its not working
A2:Count(A2:A10000).
I think i might need to use offset but not sure.

Thanks a lot

SD