|
|
|
|
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
|