Dynamic Range Names For a Graph

Hello Everyone.

I am having a lot of touble making a graph in excel and was hoping someone could please help me.

I have a table of data which is 90 rows long. The data starts on row 8.
Each day this table is updated (via a macro) and the new data goes on row 8, and the remaining data is shifted down a row.. ie.. the data on row 8 moves to row 9 etc.. etc..

I am trying to graph the first 15 rows of this table. Lets say j8:J23 and n8:n23 (ie I am graphing the 15 most recent days of data)

each time a row is inserted, the row references in the source data for the graph keep changing ie $j$8 would become $j$9..

Could anyone please tell me how to stop the row reference from changing each time a new line is inserted in my table.

I have looked at some examples of a Dynamic Range Name but for the life of me I cant get it to work arggg!!

Any help would be greatly appreciated.

Kind Regards
W

Hi,

Stick with the dynamic charting approach. Create two named ranges for
the category labels and data

ChtLabel: =OFFSET(INDIRECT("Sheet1!$J$8"),0,0,15,1)
ChtData: =OFFSET(ChtLabel,0,4)

Then the series formula for your chart would be something like,
=SERIES(,Book2!ChtLabel,Book2!ChtData,1)

Cheers
Andy

whiZZfiZZ wrote:
> Hello Everyone.
>
> I am having a lot of touble making a graph in excel and was hoping
> someone could please help me.
>
> I have a table of data which is 90 rows long. The data starts on row
> 8.
> Each day this table is updated (via a macro) and the new data goes on
> row 8, and the remaining data is shifted down a row.. ie.. the data on
> row 8 moves to row 9 etc.. etc..
>
> I am trying to graph the first 15 rows of this table. Lets say j8:J23
> and n8:n23 (ie I am graphing the 15 most recent days of data)
>
> each time a row is inserted, the row references in the source data for
> the graph keep changing ie $j$8 would become $j$9..
>
> Could anyone please tell me how to stop the row reference from changing
> each time a new line is inserted in my table.
>
> I have looked at some examples of a Dynamic Range Name but for the life
> of me I cant get it to work arggg!!
>
> Any help would be greatly appreciated.
>
> Kind Regards
> W
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info