Finding the last 12 rows of a range

[john434]

Hi all

Right, i'm trying to create a dynamic range that will return the last/bottom 12 rows from a range.

I need to produce charts which show performance on a rolling 12 month period, i currently have a table which is set out something like this:

Date Actual % Target
Jan 06 97% 90%
Feb 06 87% 90%
Mar 06 92% 90%

Every month new figures are added to the bottom of this table, i'd like to be able to pick up the last/bottom 12 enteries automatically so the chart gets updated and we still have all the previous months data.

Any help would be brilliant.

Cheers

 

[SkipVought]

Hi,

Take a look at the OFFSET function along with COUNTA.

You should end up with something like...
[tt]
=offset(Sheet1!$A$1,counta(yourrange)-11,0,12,number of columns in range)
[/tt]


Skip,

_[red][/red]

 

[xlbo]

dynamic named range:

assuming that you have data in A1:C100 (last row doesn't really matter)

Insert>Name>Define

copy and paste the following into the refers to box

=offset(A1,counta(A:A)-12,,12,1)

you will need to do this for each seies that you need to include in the chart. Amend the column references as appropriate for your data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[john434]

This is brilliant!

Could either of you do me a favour and break both these formula down, to help me understand actually how they work. I've used dynamic ranges before, but i'm getting lost at the:

-11,0,12,number of columns in range)(Skip)
and the
-12,,12,1)(xlbo) stage.

They both seem to work, yet they are different???

Thanks guys

 

[xlbo]

Skip is showing you how to do a dyna range for ALL your data - mine is for 1 column

I would be surprised that the -11 doesn't miss the last row of your data however.....

the nested calcs in the formaula return numbers which are then used by the formula instead of hard coding

=OFFSET(Start Position, No. Of Rows Down to start, No. Of Columns across to start, Rows in range, Columns in Range)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question