is there a code method for dynamic arrays for graphing?

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
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
ABCDEFGHI
1dateArray1Array2Array3Array4Array5Array6Weeks
28/5/200523,561496,79749679749679749679749679752
38/12/20055,47846,79746797467974679746797
48/19/20054,51216,78916789167891678916789
58/26/20054,464521,453521453521453521453521453
69/2/200515,646167,697167697167697167697167697
79/9/2005416,789521,453521453521453521453521453
89/16/2005486,75678,99578995789957899578995
99/23/20054,67985,64785647856478564785647
109/30/2005797,131847,599847599847599847599847599
1110/7/200597,94184,75184751847518475184751
1210/14/2005867,89796,85796857968579685796857
1310/21/2005496,78948,71248712487124871248712
1410/28/200556,78954,75654756547565475654756
1511/4/200586,78999,99999999999999999999999
1611/11/2005165,79889,89889898898988989889898
1711/18/2005156,498165,798165798165798165798165798
1811/25/200558,475156,498156498156498156498156498
1912/2/2005789,64191,65158475584755847558475
2012/9/2005976,45178,432789641789641789641789641
Sheet1
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
HI

Try

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


Tony
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Jun 3, 2006
Messages
550

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Jun 3, 2006
Messages
550
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:

Code:
=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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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:

Code:
=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]
 

Forum statistics

Threads
1,136,260
Messages
5,674,693
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top