Use a NAME in a NAME?

[dbasmb]

I have 7 columns of data I enter daily into Excel. That data is automatically graphed. However, I must set the range of the data in order to properly graph it. So each day, I must use the Name Manager to change the range of the data to reflect the new data just added. Is there another way to do that rather than changing each variable's range to the new row the data resides in. I'd envision something like this:

Name: current_end_of_range ----- Refers to: =$F$4642
Name: DateRange ----- Refers to: =Sheet1!$F10$:current_end_of_range

OR, more ideally:

Name: current_row ----- Refers to: =4642
Name: DateRange ----- Refers to: =Sheet1!$F10$:$F$current_row

but, of course, I cannot do it either of those ways. Is there a way that works?

Thanks for your help!

Doug

[MarvinP]

Hi dbasmb and welcome to the forum,

Excel has thought of this problem and the topic is Dynamic Named Ranges.
Read:
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.beyondtechnology.com/geeks007.shtml or
http://www.contextures.com/xlNames01.html

[teylyn]

Hello,

if you already have the addresses established in the range names, then you can use Index to pinpoint the range.

Name: current_end_of_range ----- Refers to: =$F$4642
Name: DateRange ----- Refers to: =Sheet1!$F10$:current_end_of_range

I assume you mean =Sheet1!$F$10 instead of =Sheet1!$F10$, and assuming that current_end_of_range is on Sheet1, you can reference the range in a formula, for example Sum() like this:

=sum(Sheet1!$F10$:current_end_of_range)

or if you want to use only the row number:

Please Login or Register  to view this content.

=sum(Sheet1!$F10$:index(Sheet1!$F:$F,current_row))

Of course you can also use named ranges in the definition of other named ranges. Actually, that's one of their major uses.

MyRange refers to =Sheet1!$F10$:index(Sheet1!$F:$F,current_row)



cheers,