Excel Named Range with a twist 2

[thefox149]

Hi all

I am comfortable with creating dynamic named ranges for charts but I wouls like to create a named range that uses the last 12 rows in the column...any suggestions I know you can use offset but my usual dynamic

My cat's name is sprinkles
-Ralph Wigam

 

[GlennUK]

Use an OFFSET within an OFFSET:

=OFFSET(OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,1,1),-11,0,12,1)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[firefytr]

Hi there Ralph,

This named range should give you what you need ...


=OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B:$B)-13,0,12)


Assuming that the data is on Sheet 1 with a single line header in row 1 and your data starts in row 2 going down (with nothing else in the column and no blank rows).

HTH

-----------
Regards,
Zack Barresse

 

[firefytr]

Oh, also, here is a tutorial on some dynamic charting ...

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html#BasicRange

HTH

-----------
Regards,
Zack Barresse

 

[GlennUK]

Hi thefox149,

of course firefytr's formula is better, it's first thing in the morning here, and I'm not thinking straight ... should have calculated the offset and used a single OFFSET function instead of nesting them.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[thefox149]

thanks guys ...have a star each

My cat's name is sprinkles
-Ralph Wigam

 

[firefytr]

Well, thanks! Not really sure what a star is, but it sounds nice.

Glad it works for you.

-----------
Regards,
Zack Barresse

 

[xlbo]

Zack - them's the little purple stars next to your name - people are encouraged to give them if an answer has been helpful etc. This then allows others to search the archives more effectively, looking for "starred" posts.

It's also the TT way of saying thanks

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

 

[firefytr]

Thanks for hte info xlbo!

-----------
Regards,
Zack Barresse