Charting Data That can grow

[chimp]

i have a graph that uses data generated by access, but the problem i
have is that the graph is setup using a XLT.

i am capturing data by week, so each week that my graph is generated it
will included more data how do i set it up so that the graph is
adjusted accordingly.

i was pointed to the following page but really dont understand it.

http://tinyurl.com/2bqhk

i have had a look and aint got a clue, what that meant.....

my sheet containing the data is claaed:

DATA

and my sheet layout is as follows:

[image: http://members.lycos.co.uk/redtilefc/1.jpg]

i have looked at the calculations and have no idea of how to apply
these...

The Instruction State:

Create the names

'Basic Range'!XValues =OFFSET('Basic Range'!YValues,0,-1)
'Basic Range'!YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic
Range'!$B:$B)-1,1)

where 'Basic Range' is the name of the worksheet containing this
example. The '-1' in the definition of YValues adjusts for the cell
containing the word 'Price' (cell B4). Also, one must be careful and
ensure that nothing else is entered in any cell in column B -- at least
not without adjusting the formula above.

AM I RIGHT IN THINKING THAT I NEED TO NAME A RANGE?? IF SO THEN I AM
NOW REALLY STUMPED...

BUT I AM ALSO UNSURE OF THE CALCULATION,

SHOULD BE

DATA'!XVALUES =OFFSET('DATA'!YVALUES,0,-1)
'DATA'!YVALUES =OFFSET('DATA'!$B$5,0,0,COUNTA('BASIC RANGE'!$B:$B)-1,1)



NOT SURE WHAT THE CELL REFERENCES SHOULD BE THOUGH
AM I RIGHT IN THINKING THE FOLLOWING SIMPLY MAKES REFERENCE TO THE
NAMED RANGES??

AND SHOULD BE INSERTED INTO THE VALUES BOX OF THE SOURCE DATA WINDOW.


The next and final step is to create a chart with the formula
=SERIES(,'Basic Range'!XValues,'Basic Range'!YValues,1)

Please help i am really stuck here

Andy

 

[Debra Dalgleish]

Perhaps it will help if you look at another example. Jon Peltier has
instructions for a dynamic chart:

http://www.peltiertech.com/Excel/Charts/DynamicLast12.html

i have a graph that uses data generated by access, but the problem i
have is that the graph is setup using a XLT.

i am capturing data by week, so each week that my graph is generated it
will included more data how do i set it up so that the graph is
adjusted accordingly.

i was pointed to the following page but really dont understand it.

http://tinyurl.com/2bqhk

i have had a look and aint got a clue, what that meant.....

my sheet containing the data is claaed:

DATA

and my sheet layout is as follows:

[image: http://members.lycos.co.uk/redtilefc/1.jpg]

i have looked at the calculations and have no idea of how to apply
these...

The Instruction State:

Create the names

'Basic Range'!XValues =OFFSET('Basic Range'!YValues,0,-1)
'Basic Range'!YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic
Range'!$B:$B)-1,1)

where 'Basic Range' is the name of the worksheet containing this
example. The '-1' in the definition of YValues adjusts for the cell
containing the word 'Price' (cell B4). Also, one must be careful and
ensure that nothing else is entered in any cell in column B -- at least
not without adjusting the formula above.

AM I RIGHT IN THINKING THAT I NEED TO NAME A RANGE?? IF SO THEN I AM
NOW REALLY STUMPED...

BUT I AM ALSO UNSURE OF THE CALCULATION,

SHOULD BE

DATA'!XVALUES =OFFSET('DATA'!YVALUES,0,-1)
'DATA'!YVALUES =OFFSET('DATA'!$B$5,0,0,COUNTA('BASIC RANGE'!$B:$B)-1,1)



NOT SURE WHAT THE CELL REFERENCES SHOULD BE THOUGH
AM I RIGHT IN THINKING THE FOLLOWING SIMPLY MAKES REFERENCE TO THE
NAMED RANGES??

AND SHOULD BE INSERTED INTO THE VALUES BOX OF THE SOURCE DATA WINDOW.


The next and final step is to create a chart with the formula
=SERIES(,'Basic Range'!XValues,'Basic Range'!YValues,1)

Please help i am really stuck here

Andy

 

[chimp]

right, i have had a look at the examples you have sent me thanks....

however i am still a little stuck.

whenever i try to enter the data into the Graph Source data boxes

=Fault_occur.xlt!Labels

, i get the following message:


YOUR FORMULACONTAINS AN INVALID EXTERNAL REFERNECE TO A WORKSHEET.

VERIFY THE PATH, WORKBOOK AND RANGE NAME OR CELL REFERENCE ARE CORRECT
AND TRY AGAIN



my workbook is set up as follows:

Title : Fault_Occur.xlt
Worksheets: (2 of)

Chart
Data

Named Ranges: (2 of)

Labels - =OFFSET(Data!$A$2,,,COUNTA(Data!$A$2:$A$200),1)
Values - =OFFSET(Data!$E$2,,,COUNTA(Data!$E$2:$E$200),1)

the trouble i seem to get happens when i try to change the source dat
on the graph page.

currently i have

Data Range - =Data!$A$1:$A$20,Data!$E$1:$E$20

Series

Name - =Data!$E$1
Values - =Data!$E$2:$E$20
Category x Labels - =Data!$A$2:$A$20

i am wondering if the trouble is possibly cos it is an xlt file and no
an xls file....should this make any difference.

please help this is reallt doing my head in....

happy to email my template to anyone that can help.

cheers

And