OFFSET to get last 52 occupied cells in range

[Thebatfink]

Hi,

I'm trying to get an offset formula to do the following..

Return me the range $A$5:LASTOCCUPIEDCELLINCOLUMN - 52 cells up.

So I basically end up with a range of the last 52 occupied cells in A:A.

I want this for a dynamic chart with a 12 month rolling update. I know this is available http://peltiertech.com/Excel/Charts/DynamicLast12.html but I'm struggling to modify it to my needs.

Any help would be greatly appreciated.

Thanks
Batfink!

 

[SteveO59L]

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

lastrow=lastrow-52

range("a5:a" & lastrow).select

 

[Thebatfink]

Hey, thanks for the reply. Sorry I probably wasn't specific enough though, I need it in a formula so I can use it for a named range for the series on a chart. Not VBA.

I'm currently using this to find all the occupied cells in the column minus the headers, any way of modifying this to find that last cell and then count up 52 rows?

=OFFSET('Data'!$A$3,2,0,COUNTA('Data'!$A:$A)-1,1)

Thanks!

 

[Darren Bartrup]

Something like:

=OFFSET($A$1,COUNTA($A:$A)-52,1,COUNTA($A:$A)-(COUNTA($A:$A)-52))

 

[Thebatfink]

Excellent

Just to exclude my column headers and empty padding cell at the top of the sheet, just changed to

=OFFSET($A$4,COUNTA($A:$A)-52,1,COUNTA($A:$A)-(COUNTA($A:$A)-52))

Works fantastically. Thank you very much for your time!

 

[Peter_SSs]

Something like:

=OFFSET($A$1,COUNTA($A:$A)-52,1,COUNTA($A:$A)-(COUNTA($A:$A)-52))

Surely
COUNTA($A:$A)-(COUNTA($A:$A)-52)
is just 52?

 

[Darren Bartrup]

Surely
COUNTA($A:$A)-(COUNTA($A:$A)-52)
is just 52?

It's my Friday today & I've got my head stuck in SQL Server & SSIS today. Nothing's making sense.
Leave me alone. :D

 

[Thebatfink]

So

=OFFSET($A$4,COUNTA($A:$A)-52,1,52)
[\code]

??

I was just thinking .. Its not a problem in my current table but I can see me using this again for sure in other ways.. How could I capture the range being under 52 rows in size, but keeping one offset formula so it would work with less than and more than 52 rows? (range is all rows if under 52 and then starts last 52 if equal to or greater than 52 rows of total data)!!

:)

 

[Peter_SSs]

Try
=OFFSET($A$4,MAX(0,COUNTA($A:$A)-52),1,MIN(COUNTA(A:A),52))