To enhance the chart from Episode 776, I'll use option buttons on the worksheet from the Forms toolbar. Episode 777 will show you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, this is day 2 of working on Lucy's chart.
Yesterday, we set up a set of formulas.
That way, if we choose number 1, 2, or 3 here in cell A7, we get a different chart showing either UNDER, AVERAGE, or OVER with different colors.
Now, to add in the option buttons, we're going to go to VIEW and TOOLBARS, and choose the FORMS toolbar.
Now, in the FORMS toolbar, we have the OPTION BUTTON setting here.
I'll choose that icon and basically drag in the worksheet to create my first option.
Now, they give me the unimaginative name of OPTION BUTTON 6.
I'm going to change this to be UNDER, and I want to make exact copies of this so I click on the border, hold down the CONTROL key, and drag.
That's going to be my AVERAGE, and then my OVER.
So, I'll make this one be OVER, and right-click.
I can now change the text here to be AVERAGE.
Perfect.
Now, I'm going to go into the first option button, then choose FORMAT CONTROL, and there's a setting here called CELL LINK, and so I'm going to set up a cell link to cell A7, click OK, and what you'll see happens now, once we set up that cell link, all of these option buttons work together.
If we choose the first one, we get a 1 in that cell, and then a 2, and then a 3.
So, you have to be really careful about the order that you add these to the worksheet.
Excel will remember that.
I was careful to make the one that I wanted to be 1, I drew that in first, and then 2, and then 3, and when I click on any of these now, the chart automatically changes.
Well, we're almost done with Lucy's chart.
In tomorrow's netcast, we'll take a look at how to solve the legend problem and actually get the correct title on the chart.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, this is day 2 of working on Lucy's chart.
Yesterday, we set up a set of formulas.
That way, if we choose number 1, 2, or 3 here in cell A7, we get a different chart showing either UNDER, AVERAGE, or OVER with different colors.
Now, to add in the option buttons, we're going to go to VIEW and TOOLBARS, and choose the FORMS toolbar.
Now, in the FORMS toolbar, we have the OPTION BUTTON setting here.
I'll choose that icon and basically drag in the worksheet to create my first option.
Now, they give me the unimaginative name of OPTION BUTTON 6.
I'm going to change this to be UNDER, and I want to make exact copies of this so I click on the border, hold down the CONTROL key, and drag.
That's going to be my AVERAGE, and then my OVER.
So, I'll make this one be OVER, and right-click.
I can now change the text here to be AVERAGE.
Perfect.
Now, I'm going to go into the first option button, then choose FORMAT CONTROL, and there's a setting here called CELL LINK, and so I'm going to set up a cell link to cell A7, click OK, and what you'll see happens now, once we set up that cell link, all of these option buttons work together.
If we choose the first one, we get a 1 in that cell, and then a 2, and then a 3.
So, you have to be really careful about the order that you add these to the worksheet.
Excel will remember that.
I was careful to make the one that I wanted to be 1, I drew that in first, and then 2, and then 3, and when I click on any of these now, the chart automatically changes.
Well, we're almost done with Lucy's chart.
In tomorrow's netcast, we'll take a look at how to solve the legend problem and actually get the correct title on the chart.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
