Charting by column name

B

Bryan Kelly

We have two systems that produce similiar data and need to chart both in a
similiar manner. However, the exact columns are not the same and may even
move from time to time. I need a macro that does the following:

Find the columns containing "Elapsed Time", "AZ", "EL" and chart them with
an XY scatter chart with lines connecting the points. The column "Elapsed
Time" must the the X axis, but I can guarentee it will be to the left of AZ
and EL. These are antenna pointing angles and I need to compare the
performance of two systems.


Thank you,
Bryan Kelly
Time is the medium we use to express out priorities.
 
J

Jon Peltier

Bryan -

You can do this with dynamic ranges. Assuming your labels are in row 1
of Sheet1, here are some defined names that construct the X and Y values
for your chart. Press CTRL+F3 to open the Defined Name dialog, and
define these names:

Name: ET
Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0)

Name: AZ
Refers To: =MATCH("AZ",Sheet1!$1:$1,0)

Name: EL
Refers To: =MATCH("EL",Sheet1!$1:$1,0)

(ET, AZ, and EL are the column numbers where these labels are found.)

Name: Xrows
Refers To:
=COUNT(INDEX(Sheet1!$1:$65536,1,ET):INDEX(Sheet1!$1:$65536,65536,ET))

(This is the number of rows used in the Elapsed Time column)

Name: TheX
Refers To: =(INDEX(Sheet1!$1:$65536,2,ET):INDEX(Sheet1!$1:$65536,Xrows,ET))

Name: TheAZ
Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65536,Xrows,AZ))

Name: TheEL
Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65536,Xrows,EL))

(These are the data ranges under the labels.)

Now use the chart wizard to create a chart. In step 2, click on the
Series tab, Select or Add the first series, name it "AZ", and enter
=Sheet1!theX in the X Values box and =Sheet1!theAZ in the Y Values box.
Add the second series, again enter =Sheet1!theX in the X Values box, and
enter =Sheet1!theEL in the Y Values box. No matter which column has the
particular labels (and the left to right alignment doesn't matter), the
dynamic ranges will find the appropriate data for the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
B

Bryan Kelly

Thank you Jon,
Its a bit late tonight, but I forwarded this to myself at work and will be
integrating it tomorrow.
I will be on travel for a week, and will not be able to respond.

Please note, when I posted my thank you note to this thread, I did have you
in mind.
Thank you,
Bryan
 
Top