OFFSET to get last 52 occupied cells in range

Thebatfink

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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

lastrow=lastrow-52

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

Thebatfink

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

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Something like:

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

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397

ADVERTISEMENT

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!
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,216
Office Version
  1. 365
Platform
  1. Windows
Try
=OFFSET($A$4,MAX(0,COUNTA($A:$A)-52),1,MIN(COUNTA(A:A),52))
 

Watch MrExcel Video

Forum statistics

Threads
1,133,562
Messages
5,659,556
Members
418,506
Latest member
drafting3

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
Top