I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

I saw this question before and I wasn't clear on what you wanted to do.
Can you give some more specifics? Examples of the specific information you
want in the charts would be helpful. X = what? Y = What?

"Fysh" wrote in message
...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from
several
worksheets by using code. On this worksheet is approximately 15-20
columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate
information.
Another thing as time goes on information will be added. I looked at
pivot
table and a few websites, but can't seem to get anything what I am
looking.
Can anyone assist on this? Thanks in advance

Thanks for the quick response

I would like a line chart with
x= trimesters and year
y= the column selected

Some columns contain averages of scores of a particluar trimester of a
particular year. Every other column contains passing % for a particluar type
of test within a particular trimester in a a particular year. What I would
like to be able to show the scores on a chart depending on which column is
selected. I took a look at the site and it gives some idea of what I want to
do, but not quite sure on how to go about doing it.
http://www.tushar-mehta.com/excel/ne...html#OneColumn

I have worked quite a bit in Access, but my skills in Excel are somewhat
limited.

Thanks again for any assistance.

"Barb Reinhardt" wrote:

I saw this question before and I wasn't clear on what you wanted to do.
Can you give some more specifics? Examples of the specific information you
want in the charts would be helpful. X = what? Y = What?

"Fysh" wrote in message
...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from
several
worksheets by using code. On this worksheet is approximately 15-20
columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate
information.
Another thing as time goes on information will be added. I looked at
pivot
table and a few websites, but can't seem to get anything what I am
looking.
Can anyone assist on this? Thanks in advance

I would probably concatenate the Year and Trimester in another column for use as your X variable. You can do this with the concatenate function or using an equation like

=E2&"-"&F2

Where E2 and F2 are the cells you are concatenating.

Selecting the columns (RW, RW%, WIW, WIW%) and using one graph to get the info is beyond my expertise.

"Fysh" wrote in message ...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

Ok I got it to work for the most part. I created a combo box using a range
of the titles, which populates a cell. This cell is then used in the
formulas which I got from the website that I mentioned previously. I had to
tweak it a little to get it to work. However, it looks like I will need to
create 2 different charts, one for averages and one for %. The Y column
changes to 10000 when when I select one of the columns with a %. Another
thing is I would like to combine the year with the trimesters. I don't want
it to be like 1st2001 2nd2001. Is there a way to populate this graph with
the year showing just once for each year under the 3 trimesters? Yes I could
edit the Titles under the chart options, but I don't think this would be the
best way since the chart will grow as more trimesters and years will be
added. Can anyone assist me on this? Much appreciated for any assistance.

"Barb Reinhardt" wrote:

I would probably concatenate the Year and Trimester in another column for use as your X variable. You can do this with the concatenate function or using an equation like

=E2&"-"&F2

Where E2 and F2 are the cells you are concatenating.

Selecting the columns (RW, RW%, WIW, WIW%) and using one graph to get the info is beyond my expertise.

"Fysh" wrote in message ...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

You can make Excel use both columns for the category labels. Easiest way is using
Source Data from the Chart menu, and on the Series tab, select the first series, and
then in the Categories box, select this two column region.

If you make the first two columns look like this:

2001 1st
2nd
3rd
2002 1st
2nd
3rd
2003 1st
2nd
3rd
2003 1st
2nd
3rd

the axis will look like this:

1st 2nd 3rd 1st 2nd 3rd 1st 2nd 3rd 1st 2nd 3rd
2001 2002 2003 2004

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


Fysh wrote:

Ok I got it to work for the most part. I created a combo box using a range
of the titles, which populates a cell. This cell is then used in the
formulas which I got from the website that I mentioned previously. I had to
tweak it a little to get it to work. However, it looks like I will need to
create 2 different charts, one for averages and one for %. The Y column
changes to 10000 when when I select one of the columns with a %. Another
thing is I would like to combine the year with the trimesters. I don't want
it to be like 1st2001 2nd2001. Is there a way to populate this graph with
the year showing just once for each year under the 3 trimesters? Yes I could
edit the Titles under the chart options, but I don't think this would be the
best way since the chart will grow as more trimesters and years will be
added. Can anyone assist me on this? Much appreciated for any assistance.

"Barb Reinhardt" wrote:


I would probably concatenate the Year and Trimester in another column for use as your X variable. You can do this with the concatenate function or using an equation like

=E2&"-"&F2

Where E2 and F2 are the cells you are concatenating.

Selecting the columns (RW, RW%, WIW, WIW%) and using one graph to get the info is beyond my expertise.

"Fysh" wrote in message ...

I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

Thanks for both of your suggestions. Sorry for the delay in the response, I
will try both of your suggestions and will write back if I run into problems.
Once again thanks.

"Fysh" wrote:

Ok I got it to work for the most part. I created a combo box using a range
of the titles, which populates a cell. This cell is then used in the
formulas which I got from the website that I mentioned previously. I had to
tweak it a little to get it to work. However, it looks like I will need to
create 2 different charts, one for averages and one for %. The Y column
changes to 10000 when when I select one of the columns with a %. Another
thing is I would like to combine the year with the trimesters. I don't want
it to be like 1st2001 2nd2001. Is there a way to populate this graph with
the year showing just once for each year under the 3 trimesters? Yes I could
edit the Titles under the chart options, but I don't think this would be the
best way since the chart will grow as more trimesters and years will be
added. Can anyone assist me on this? Much appreciated for any assistance.

"Barb Reinhardt" wrote:

I would probably concatenate the Year and Trimester in another column for use as your X variable. You can do this with the concatenate function or using an equation like

=E2&"-"&F2

Where E2 and F2 are the cells you are concatenating.

Selecting the columns (RW, RW%, WIW, WIW%) and using one graph to get the info is beyond my expertise.

"Fysh" wrote in message ...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

One option is a PivotTable, but that has its own idiosyncracies. The
other option is to extend the method you've experimenting with and
merge it with a method that creates the kind of labels you want.

Suppose your data are in A1:F10 (well, that's where I put my test
data). I also had the luxury of messing with the layout. If you
don't, you will have to adapt the idea below by using, say, another
worksheet.

Create a 'hole' in C:D by selecting those columns, right-clicking, and
Insert. In C1 and D1 copy the headers from A & B. In C2 enter the
formula =IF(A1=A2,"",A2). In D2, enter =B2. Copy C2:D2 as far down as
you have data in columns A & B.

Create the names:
_Y1
=OFFSET(Sheet1!$E$2,0,(Sheet1!$I$1-1)*2,COUNTA(Sheet1!$A:$A)-1,1)

_Y1percent
=OFFSET(_Y1,,1)

XVals
=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

Create the combo box so that its value is in cell I1. If you use the
control from the Forms toolbar, it will generate numbers such as 1, 2,
etc.

Create a chart (use Line or Column). The 2 series should be:
=SERIES(Sheet1!$E$1,Book3!XVals,Book3!_Y1percent,1 )
=SERIES(Sheet1!$E$1,Book3!XVals,Book3!_Y1,2)
Double-click one of the series; from the Axis tab select Secondary.

That's it (other than the aesthetics of adding a dynamic label and the
like).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

OK I used some of your suggestion and incorporated it into what I have.
However, for some reason when my point is 100 or 100% my scale changes to max
120 or 120%. Is there a way to keep the max 100 or 100%? If I change it on
the axis then it messes up on my other charts. I would like to keep 100 or
100% there all the time rather than having it change. Thanks


"Tushar Mehta" wrote:

One option is a PivotTable, but that has its own idiosyncracies. The
other option is to extend the method you've experimenting with and
merge it with a method that creates the kind of labels you want.

Suppose your data are in A1:F10 (well, that's where I put my test
data). I also had the luxury of messing with the layout. If you
don't, you will have to adapt the idea below by using, say, another
worksheet.

Create a 'hole' in C:D by selecting those columns, right-clicking, and
Insert. In C1 and D1 copy the headers from A & B. In C2 enter the
formula =IF(A1=A2,"",A2). In D2, enter =B2. Copy C2:D2 as far down as
you have data in columns A & B.

Create the names:
_Y1
=OFFSET(Sheet1!$E$2,0,(Sheet1!$I$1-1)*2,COUNTA(Sheet1!$A:$A)-1,1)

_Y1percent
=OFFSET(_Y1,,1)

XVals
=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

Create the combo box so that its value is in cell I1. If you use the
control from the Forms toolbar, it will generate numbers such as 1, 2,
etc.

Create a chart (use Line or Column). The 2 series should be:
=SERIES(Sheet1!$E$1,Book3!XVals,Book3!_Y1percent,1 )
=SERIES(Sheet1!$E$1,Book3!XVals,Book3!_Y1,2)
Double-click one of the series; from the Axis tab select Secondary.

That's it (other than the aesthetics of adding a dynamic label and the
like).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance

How does it mess up on the other charts? If the axis stayed at 100 because you set
the axis scale or by some other magic, what's the difference? How would the
unspecified magic not mess up the other charts?

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

Fysh wrote:

OK I used some of your suggestion and incorporated it into what I have.
However, for some reason when my point is 100 or 100% my scale changes to max
120 or 120%. Is there a way to keep the max 100 or 100%? If I change it on
the axis then it messes up on my other charts. I would like to keep 100 or
100% there all the time rather than having it change. Thanks


"Tushar Mehta" wrote:


One option is a PivotTable, but that has its own idiosyncracies. The
other option is to extend the method you've experimenting with and
merge it with a method that creates the kind of labels you want.

Suppose your data are in A1:F10 (well, that's where I put my test
data). I also had the luxury of messing with the layout. If you
don't, you will have to adapt the idea below by using, say, another
worksheet.

Create a 'hole' in C:D by selecting those columns, right-clicking, and
Insert. In C1 and D1 copy the headers from A & B. In C2 enter the
formula =IF(A1=A2,"",A2). In D2, enter =B2. Copy C2:D2 as far down as
you have data in columns A & B.

Create the names:
_Y1
=OFFSET(Sheet1!$E$2,0,(Sheet1!$I$1-1)*2,COUNTA(Sheet1!$A:$A)-1,1)

_Y1percent
=OFFSET(_Y1,,1)

XVals
=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

Create the combo box so that its value is in cell I1. If you use the
control from the Forms toolbar, it will generate numbers such as 1, 2,
etc.

Create a chart (use Line or Column). The 2 series should be:
=SERIES(Sheet1!$E$1,Book3!XVals,Book3!_Y1percent ,1)
=SERIES(Sheet1!$E$1,Book3!XVals,Book3!_Y1,2)
Double-click one of the series; from the Axis tab select Secondary.

That's it (other than the aesthetics of adding a dynamic label and the
like).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

I posted here a few days ago and was given some ideas on how to do this.
However, I seem lost. I have a main worksheet which is updated from several
worksheets by using code. On this worksheet is approximately 15-20 columns
something like this.
trimester year RW RW% WIW WIW% etc
1st 2001 80 89% 79 75%
2nd 2001 90 89% 84 87%
3rd 2001 87 88% 92 89%
1st 2002 88 88% 90 88%
etc etc

What I would like to do is create one chart and by selecting the title of
the column from either a combo box or by using a button at the top of each
column have it poplulate the chart with trimester, year and title. Also,
have the title and legend of the chart change to the appropriate information.
Another thing as time goes on information will be added. I looked at pivot
table and a few websites, but can't seem to get anything what I am looking.
Can anyone assist on this? Thanks in advance