Reference a changing range

S

SD

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
 
B

Barb Reinhardt

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)
 
S

SD

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.
 
B

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)
 
C

Cat

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
 
Top