OFFSET to get last 52 occupied cells in range

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
410
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

lastrow=lastrow-52

range("a5:a" & lastrow).select
 
Upvote 0
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?

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

Thanks!
 
Upvote 0
Something like:

Code:
=OFFSET($A$1,COUNTA($A:$A)-52,1,COUNTA($A:$A)-(COUNTA($A:$A)-52))
 
Upvote 0
Excellent :)

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

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

Works fantastically. Thank you very much for your time!
 
Upvote 0
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
 
Upvote 0
So

Code:
=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)!!

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

Forum statistics

Threads
1,218,539
Messages
6,143,085
Members
450,461
Latest member
Bosavon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top