is there a code method for dynamic arrays for graphing?

[jmckeone]

I've always found the OFFSET function to be one of the more confusing an convoluted of microsoft's menagerie. Being that I do a great deal of charting automation would be a great asset.

Below is a truncated example of my layout. The items in the title row to serve as the range name. The offset to come from the cell at the upper right so I can change graphs on the fly.

I need to be able to cycle through the columns creating a named offset range for each from cell 2 to the last used row.

Programtest1.xls
	A	B	C	D	E	F	G	H	I
1	date	Array1	Array2	Array3	Array4	Array5	Array6		Weeks
2	8/5/2005	23,561	496,797	496797	496797	496797	496797		52
3	8/12/2005	5,478	46,797	46797	46797	46797	46797
4	8/19/2005	4,512	16,789	16789	16789	16789	16789
5	8/26/2005	4,464	521,453	521453	521453	521453	521453
6	9/2/2005	15,646	167,697	167697	167697	167697	167697
7	9/9/2005	416,789	521,453	521453	521453	521453	521453
8	9/16/2005	486,756	78,995	78995	78995	78995	78995
9	9/23/2005	4,679	85,647	85647	85647	85647	85647
10	9/30/2005	797,131	847,599	847599	847599	847599	847599
11	10/7/2005	97,941	84,751	84751	84751	84751	84751
12	10/14/2005	867,897	96,857	96857	96857	96857	96857
13	10/21/2005	496,789	48,712	48712	48712	48712	48712
14	10/28/2005	56,789	54,756	54756	54756	54756	54756
15	11/4/2005	86,789	99,999	99999	99999	99999	99999
16	11/11/2005	165,798	89,898	89898	89898	89898	89898
17	11/18/2005	156,498	165,798	165798	165798	165798	165798
18	11/25/2005	58,475	156,498	156498	156498	156498	156498
19	12/2/2005	789,641	91,651	58475	58475	58475	58475
20	12/9/2005	976,451	78,432	789641	789641	789641	789641
Sheet1

 

[acw]

HI

Try

=offset($a$1,0,0,$I$2+1,7) as the formula for your dynamic name.


Tony

 

[jmckeone]

Let me take another stab at describing what I'm endeavoring to do.

Eventually each column's data will be used to create a dynamic chart. My understanding is that for a chart to be dynamic the data must utilize the OFFSET command. The best way I've seen in the past to do this has been to name a range with the underlying formula as an OFFSET command.

This is part of a larger task. My idea, someone feel free to tell me if there is a better way to make this happen, is to loop through the columns (there can be 50-100 at times) naming each and giving it an underlying offset formula. The second phase would be to then create a graph for each of the named arrays. An using an array containing refernce points on a seperate sheet place the graphs accordingly.

I had expected macro code could be written to allow me to perform this process on the fly. Unfortunately to this point my understanding of code has been limited thought I've been able to piece together snippets of code I do understand to accomplish many tasks.

 

[acw]

Hi

From the sample data, would you have 1 graph with 6 data items, or do you want to be able to have 6 different graphs?


Tony

 

[jmckeone]

6 graphs ... one per column of data in the example above ... the destination of the graphs would be on a second sheet (we'll call the sheets sheet1 and sheet2 for simplicity) at A1, A25, A49, A73, A97 and A121.

 

[acw]

Hi

Ok try
Name: Xvalues
Refers to: =OFFSET(Sheet1!$A$2,0,0,Sheet1!$H$2,1)

Then you can have a series of Ydata entries like
Name: YData1
refers to: =OFFSET(Xvalues,0,1)

Name: YData2
Refers To: =OFFSET(Xvalues,0,2)

etc

Each graph has the same Xvalues, but has a different Ydata entry.

Change the value in H2 and all the graphs will update.

HTH

Tony

 

[jmckeone]

This still leaves the initial question unanswered ... Not that I don't appreciate the formula efforts on your part.

After a bit of tinkering with the forumla I came up with this for the XValues to show the most recent items:

=OFFSET(Sheet1!$A$2,COUNTA('Sheet1'!$B$2:$B$200)-'Sheet1'!$I$2,0,'Sheet1'!$I$2,1)

How do I write VBA code to perform this by simply running a macro?[/code]

 

[tusharm]

Turn on the macro recorder (Tools | Macro > Record new macro...), create a named formula by hand, and turn off the recorder. XL should give you the necessary code to create one formula. You can then adapt the code to loop through however many columns and create however many named formulae.

This still leaves the initial question unanswered ... Not that I don't appreciate the formula efforts on your part.

After a bit of tinkering with the forumla I came up with this for the XValues to show the most recent items:

=OFFSET(Sheet1!$A$2,COUNTA('Sheet1'!$B$2:$B$200)-'Sheet1'!$I$2,0,'Sheet1'!$I$2,1)

How do I write VBA code to perform this by simply running a macro?[/code]