Reference a changing range

[Guest]

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

 

[Guest]

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)

 

[T. Valko]

See if this helps:

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

Biff

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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