Create a dropdown that will display a different chart on your worksheet!
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:
MrExcel podcast is brought to you by ”Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Jonathan.
Jonathan has a very interesting question, he wants to have a drop-down on a sheet, and when he changes something from that drop-down, he wants to display a different chart on the sheet.
Alright, so I have an interesting way to do this, and what I came up with was, I created all of the 5 or 6 different charts that I wanted to show.
And then down here, out of sight, on the different worksheets I pasted those charts in a very specific place.
So the first chart went in cell B30, the next chart is in cell B50.
So every 20 rows I put a new chart, and that's important, we're going to come back to that in a bit, so I space these out perfectly down below, where no one would see them.
It's very important that they're all in the same sheet, they don't have to be on this sheet, but they have to all be together on the same sheet.
Now, let's talk about how I set up the drop-down list, that's Data Validation using the List setting.
So over here to the right-hand side, I came up with a list of the valid values, and next to that list I put the number of rows from row 30, where the chart starts.
So the first chart is 0 rows down, the next chart is 20 rows down, 40, 60.
And then over here finally, a VLOOKUP says “Hey, go back and see what they chose in cell B1, and then look up this table and tell me how many rows from there I want to go.” So that's stored in cell Y2, alright now, we're going to use a very difficult function called the OFFSET function, and we're going to use that function in a named range, has to be the named range.
So we're going to go into the Formulas tab, Define Name, I'll go to the Name Manager because I've already set it up, it is called MyChart.
Alright, and let's look down here, refers to the OFFSET of this sheet, the AllCharts sheet, starting in cell B30, and then it wants to know how many rows down to go.
Well, that's my answer in Y2, that's the answer from the VLOOKUP.
So if I choose 1950, goes down 0 rows, if I choose 1970, it goes down for 40 rows, alright, how many columns over?
Well that's 0, and then how wide of an area doing what?
Well, the charts were 15 rows by 7 columns, so in my case, I chose 15 and 7.
Now make sure you start with an =, make sure this first B30 is prefixed by the name of the sheet and an exclamation point, also again here.
Very important, you get that name set up correctly, so we'll click Close there.
Alright, now to actually create the chart, this is a cool trick, I'll move this out of the way, so we can see it happen again, let's just, alright.
To actually create the chart, here's what you're going to do, you're going to go down and choose the first chart that you have.
So I want to be very careful, start there in B30, choose the correct number of rows and columns, exactly what I chose back in the OFFSET function and I'm going to copy.
Alright, so Ctrl+C or right-click and copy, I choose Copy, and then I'll come up to where I want the chart to appear, PageUp, I'll just go right here, but I'm not going to Paste, I'm going to Paste as a Linked Picture.
Down here in Excel 2010 that's this icon, in the Excel 2007 you're going to look for Paste Picture Link.
In Excel 2003, this is wild, you have to hold down the Shift key when you go to edit, and then there’ll be Paste Picture Link in the Edit menu.
If you don't hold on the Shift key before you go to edit, you don't see that option.
Alright, so that gets us, basically what used to be called the Camera tool, a picture of that chart, and you see up here in the formula bar, it says that it's using B30:H44.
Well, that's what I want to change, I want to click in here and say that this is going to be =MyChart, MyChart is the named range that I created, and that updates to 1970.
So, when I change this cell, 1970, that causes the value over in Y2 to be updated, to show the right number of rows to offset, that causes the named range to dynamically update to grab a different number of rows.
And then, finally, my Camera tool here is showing a different value, very cool trick, great way to go.
Now, it's funny, as I created this, I created this data from Pivot tables, and then used the Show Report, Filter Pages to make all the Pivot tables and make all the charts.
And after going through all this hassle I said “Well you know, if your data does actually happen to be from a Pivot table, it would be much easier just to use the drop-down from the Pivot table.” Now Jonathan didn't say that his data’s a Pivot table, so I don't think this is going to apply to him.
But, you know, if it's possible that you could create a Pivot table and use the Report filter, or in Excel 2010 the slicers to go through and do that.
Now this is not a Pivot chart, it’s just a chart based on regular data, you'll see that the Pivot table is actually hidden here, so I have a Pivot table updating.
And then down below I have formulas pointing into the Pivot table, the charts created from these formulas.
That way, I don't have all the, I hate to say, weird, but the weird Pivot chart functionality happening on the chart.
I just have a regular chart, I get to, you know, format it, not worry about field buttons and strange things like that.
So, you know, another way to go.
Want to thank Jonathan for sending that question in, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Jonathan.
Jonathan has a very interesting question, he wants to have a drop-down on a sheet, and when he changes something from that drop-down, he wants to display a different chart on the sheet.
Alright, so I have an interesting way to do this, and what I came up with was, I created all of the 5 or 6 different charts that I wanted to show.
And then down here, out of sight, on the different worksheets I pasted those charts in a very specific place.
So the first chart went in cell B30, the next chart is in cell B50.
So every 20 rows I put a new chart, and that's important, we're going to come back to that in a bit, so I space these out perfectly down below, where no one would see them.
It's very important that they're all in the same sheet, they don't have to be on this sheet, but they have to all be together on the same sheet.
Now, let's talk about how I set up the drop-down list, that's Data Validation using the List setting.
So over here to the right-hand side, I came up with a list of the valid values, and next to that list I put the number of rows from row 30, where the chart starts.
So the first chart is 0 rows down, the next chart is 20 rows down, 40, 60.
And then over here finally, a VLOOKUP says “Hey, go back and see what they chose in cell B1, and then look up this table and tell me how many rows from there I want to go.” So that's stored in cell Y2, alright now, we're going to use a very difficult function called the OFFSET function, and we're going to use that function in a named range, has to be the named range.
So we're going to go into the Formulas tab, Define Name, I'll go to the Name Manager because I've already set it up, it is called MyChart.
Alright, and let's look down here, refers to the OFFSET of this sheet, the AllCharts sheet, starting in cell B30, and then it wants to know how many rows down to go.
Well, that's my answer in Y2, that's the answer from the VLOOKUP.
So if I choose 1950, goes down 0 rows, if I choose 1970, it goes down for 40 rows, alright, how many columns over?
Well that's 0, and then how wide of an area doing what?
Well, the charts were 15 rows by 7 columns, so in my case, I chose 15 and 7.
Now make sure you start with an =, make sure this first B30 is prefixed by the name of the sheet and an exclamation point, also again here.
Very important, you get that name set up correctly, so we'll click Close there.
Alright, now to actually create the chart, this is a cool trick, I'll move this out of the way, so we can see it happen again, let's just, alright.
To actually create the chart, here's what you're going to do, you're going to go down and choose the first chart that you have.
So I want to be very careful, start there in B30, choose the correct number of rows and columns, exactly what I chose back in the OFFSET function and I'm going to copy.
Alright, so Ctrl+C or right-click and copy, I choose Copy, and then I'll come up to where I want the chart to appear, PageUp, I'll just go right here, but I'm not going to Paste, I'm going to Paste as a Linked Picture.
Down here in Excel 2010 that's this icon, in the Excel 2007 you're going to look for Paste Picture Link.
In Excel 2003, this is wild, you have to hold down the Shift key when you go to edit, and then there’ll be Paste Picture Link in the Edit menu.
If you don't hold on the Shift key before you go to edit, you don't see that option.
Alright, so that gets us, basically what used to be called the Camera tool, a picture of that chart, and you see up here in the formula bar, it says that it's using B30:H44.
Well, that's what I want to change, I want to click in here and say that this is going to be =MyChart, MyChart is the named range that I created, and that updates to 1970.
So, when I change this cell, 1970, that causes the value over in Y2 to be updated, to show the right number of rows to offset, that causes the named range to dynamically update to grab a different number of rows.
And then, finally, my Camera tool here is showing a different value, very cool trick, great way to go.
Now, it's funny, as I created this, I created this data from Pivot tables, and then used the Show Report, Filter Pages to make all the Pivot tables and make all the charts.
And after going through all this hassle I said “Well you know, if your data does actually happen to be from a Pivot table, it would be much easier just to use the drop-down from the Pivot table.” Now Jonathan didn't say that his data’s a Pivot table, so I don't think this is going to apply to him.
But, you know, if it's possible that you could create a Pivot table and use the Report filter, or in Excel 2010 the slicers to go through and do that.
Now this is not a Pivot chart, it’s just a chart based on regular data, you'll see that the Pivot table is actually hidden here, so I have a Pivot table updating.
And then down below I have formulas pointing into the Pivot table, the charts created from these formulas.
That way, I don't have all the, I hate to say, weird, but the weird Pivot chart functionality happening on the chart.
I just have a regular chart, I get to, you know, format it, not worry about field buttons and strange things like that.
So, you know, another way to go.
Want to thank Jonathan for sending that question in, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
