One of my favorite tricks is to fill a textbox with the result of a formula in the worksheet. When you try this as the title in an embedded chart, you need to change the formula to point back to the original worksheet. Episode 778 shows 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, we almost have this chart working perfectly.
We can choose UNDER, AVERAGE, or OVER and Excel will change the color of the chart.
Now, we set that up in the last 2 days’ podcast.
The one last thing that really drives me crazy about this chart is the legend is showing both the red, the yellow, and the green when, in fact, we only have one of them on the chart.
So, I'm actually going to get rid of the legend.
I’m going to click on it and press the DELETE key.
I want to make our chart a little bit smaller, reduce the size of the plot area to give me some space at the top, and I'm going to add a text box, draw a nice, little text box in here, and that's going to hold the words UNDER, AVERAGE, or OVER.
I will format that text box to be a little bit larger and now, somewhere in my worksheet, I need to come up with a formula that will give me the words UNDER, AVERAGE, or OVER, and so, right here next to where the option button is writing the values 1, 2, and 3, I’ll use =CHOOSE and then, in “, if it’s a 1, we want an UNDER, if it's 2, we want AVERAGE, and if it's 3, we want OVER, put all those in “, and you see now, as we choose AVERAGE, we get the word AVERAGE there, and when we choose OVER, we get the word OVER there.
[ =CHOOSE(A7,“UNDER”,“AVERAGE”,“OVER”) ] So, that's all working well.
I'm going to come here to my text box, click on the text box so that way I have dots around the outside.
In this cell, the cell that has the formula in it, is B7 and the worksheet that I happen to be on is called the CHART worksheet.
So, with the text box selected, I'm going to click into the formula bar and type =CHART -- that's the name of the worksheet -- the !, and the cell that I want, B7.
When I press ENTER, instantly you'll see that that value appears in the text box.
[ =CHART!$B$7 ] Now, let me format the text box so it’s a little bit larger, maybe 18, and what we'll see is, as I click on the chart in the option buttons, that changes the value in B7 and that allows the text box, because there was a formula assigned to the text box, to pick up the value from the worksheet instead of having it hard-coded.
So, here, we've basically now recreated that original chart that Lucy sent in, took several days, and definitely some cool tricks along the way.
Now, at this point, if you want to rearrange the option buttons, we can do that.
I find if you right-click, we can then be able to move the option button, so I'll right click each one, maybe arrange them side by side by side, MOVE HERE, right click, MOVE HERE.
Now, I can move my chart up.
I always like to kind of resize this.
The chart is a great way to hide those secret formulas that we set up on the first day back on Monday.
Well, there you have it, great way to create an interactive chart.
It changes color as we choose the different option buttons.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
Well, we almost have this chart working perfectly.
We can choose UNDER, AVERAGE, or OVER and Excel will change the color of the chart.
Now, we set that up in the last 2 days’ podcast.
The one last thing that really drives me crazy about this chart is the legend is showing both the red, the yellow, and the green when, in fact, we only have one of them on the chart.
So, I'm actually going to get rid of the legend.
I’m going to click on it and press the DELETE key.
I want to make our chart a little bit smaller, reduce the size of the plot area to give me some space at the top, and I'm going to add a text box, draw a nice, little text box in here, and that's going to hold the words UNDER, AVERAGE, or OVER.
I will format that text box to be a little bit larger and now, somewhere in my worksheet, I need to come up with a formula that will give me the words UNDER, AVERAGE, or OVER, and so, right here next to where the option button is writing the values 1, 2, and 3, I’ll use =CHOOSE and then, in “, if it’s a 1, we want an UNDER, if it's 2, we want AVERAGE, and if it's 3, we want OVER, put all those in “, and you see now, as we choose AVERAGE, we get the word AVERAGE there, and when we choose OVER, we get the word OVER there.
[ =CHOOSE(A7,“UNDER”,“AVERAGE”,“OVER”) ] So, that's all working well.
I'm going to come here to my text box, click on the text box so that way I have dots around the outside.
In this cell, the cell that has the formula in it, is B7 and the worksheet that I happen to be on is called the CHART worksheet.
So, with the text box selected, I'm going to click into the formula bar and type =CHART -- that's the name of the worksheet -- the !, and the cell that I want, B7.
When I press ENTER, instantly you'll see that that value appears in the text box.
[ =CHART!$B$7 ] Now, let me format the text box so it’s a little bit larger, maybe 18, and what we'll see is, as I click on the chart in the option buttons, that changes the value in B7 and that allows the text box, because there was a formula assigned to the text box, to pick up the value from the worksheet instead of having it hard-coded.
So, here, we've basically now recreated that original chart that Lucy sent in, took several days, and definitely some cool tricks along the way.
Now, at this point, if you want to rearrange the option buttons, we can do that.
I find if you right-click, we can then be able to move the option button, so I'll right click each one, maybe arrange them side by side by side, MOVE HERE, right click, MOVE HERE.
Now, I can move my chart up.
I always like to kind of resize this.
The chart is a great way to hide those secret formulas that we set up on the first day back on Monday.
Well, there you have it, great way to create an interactive chart.
It changes color as we choose the different option buttons.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
