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!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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,291
Office Version
365
Platform
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,291
Office Version
365
Platform
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
45,918
Office Version
365
Platform
Windows
Try
=OFFSET($A$4,MAX(0,COUNTA($A:$A)-52),1,MIN(COUNTA(A:A),52))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,403
Messages
5,511,154
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top