Today, in Episode #1493, Mike from Athens, Greece asks how to create a control that would change a chart to show either units or revenue. Bill presents a borrowed solution from Mike Alexander at DataPigTechnologies.com, but when you see the solution, you will realize that the trick is barely a trick at all.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
*Kindle is Coming to 'The MrExcel Excel Store'! Stay Tuned!*
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
*Kindle is Coming to 'The MrExcel Excel Store'! Stay Tuned!*
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1493: Variable Chart By Dropdown.
Well, hey, everyone. Welcome back to the MrExcel netcast. I'm Bill Jelen. Now, today's question is sent in from Athens, Greece. Mike sent this in and…hey, by the way, it's a holiday week here. I know the podcasts have been sporadic.
They're going to continue to be sporadic.
I'm heading down to Florida back January 12th, so I'm going to try and get as many done as I can. [ unintelligible – 00:25 ] every other day. We'll see. We'll see how it goes, but I realize I missed the last 2 days, just had some client work I had to get done.
Mike's question though, Mike has data, MONTHS, UNITS this year, VALUES this year, UNITS last year, VALUES this year, and he was looking for some sort of a control, like, maybe a, you know, a combo box or something like that, where he said he could choose where they wanted to show units or sales, and the chart would automatically update to do this.
Okay, and I said, well, right. First of all, I'm going to do data validation, ALT+D L, data validation, ALLOW a LIST. Just type UNITS and VALUES in there, that way we can quickly change that cell using the drop-down, and then, next, this next trick I've stolen from Mike Alexander of DataPig Technologies. Mike is my co-author on the Pivot Table Data Crunching books. He's a funny guy and I got this tip from him, and I apologize to Mike. I said, Mike, this is going to solve your problem but it is just absurdly easy, right? There's no trick at all in this when you see how it's done. So, I apologized in advance.
This chart here looks like it's getting data from here but it's not. If we actually look, it's getting this data from columns O and P. Now, what’s O and P? Well, that is just an area outside of view, so, like, a couple of screens over outside of the print range where no one's going to see it, out here in O and P. Let me scroll over so you can see it. First of all, I just copy the month names over, so =A3 and copy that down, and then an IF statement. If that H2 with $ signs is UNITS, then use B3 here, otherwise get the values from C3. Similar formula here, if H2 is UNITS, then use D3, that's the values from last year, otherwise the sales from last year.
[ =IF($H$2=“Units”,B3,C3) ], [ =IF($H$2=“Units”,D3,E3) ] So, let's see if we can get this all in view at the same time. Take these 3 columns and make them less wide. Alright. So, when I choose UNITS here, you see these formulas update for UNITS, and then it was just a simple matter of creating the charts based on that data.
Insert, chart, and so on, that kind of all makes sense. To get the units and values, that's actually a textbox. I put the textbox in there. INSERT, SHAPES, TEXTBOX, and then in the formula bar, I typed an = sign and clicked on cell H2 to get that formula so that way it updates as well. [ =SHEET1!$H$2 ] So, a very nice cool trick that Mike Alexander showed me that makes people think that we're variably getting the data from here in the chart, but, in reality, we're using this helper range out to the right-hand side to solve the problem.
Well, hey. I want to thank Mike from Greece for sending that question in and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1493: Variable Chart By Dropdown.
Well, hey, everyone. Welcome back to the MrExcel netcast. I'm Bill Jelen. Now, today's question is sent in from Athens, Greece. Mike sent this in and…hey, by the way, it's a holiday week here. I know the podcasts have been sporadic.
They're going to continue to be sporadic.
I'm heading down to Florida back January 12th, so I'm going to try and get as many done as I can. [ unintelligible – 00:25 ] every other day. We'll see. We'll see how it goes, but I realize I missed the last 2 days, just had some client work I had to get done.
Mike's question though, Mike has data, MONTHS, UNITS this year, VALUES this year, UNITS last year, VALUES this year, and he was looking for some sort of a control, like, maybe a, you know, a combo box or something like that, where he said he could choose where they wanted to show units or sales, and the chart would automatically update to do this.
Okay, and I said, well, right. First of all, I'm going to do data validation, ALT+D L, data validation, ALLOW a LIST. Just type UNITS and VALUES in there, that way we can quickly change that cell using the drop-down, and then, next, this next trick I've stolen from Mike Alexander of DataPig Technologies. Mike is my co-author on the Pivot Table Data Crunching books. He's a funny guy and I got this tip from him, and I apologize to Mike. I said, Mike, this is going to solve your problem but it is just absurdly easy, right? There's no trick at all in this when you see how it's done. So, I apologized in advance.
This chart here looks like it's getting data from here but it's not. If we actually look, it's getting this data from columns O and P. Now, what’s O and P? Well, that is just an area outside of view, so, like, a couple of screens over outside of the print range where no one's going to see it, out here in O and P. Let me scroll over so you can see it. First of all, I just copy the month names over, so =A3 and copy that down, and then an IF statement. If that H2 with $ signs is UNITS, then use B3 here, otherwise get the values from C3. Similar formula here, if H2 is UNITS, then use D3, that's the values from last year, otherwise the sales from last year.
[ =IF($H$2=“Units”,B3,C3) ], [ =IF($H$2=“Units”,D3,E3) ] So, let's see if we can get this all in view at the same time. Take these 3 columns and make them less wide. Alright. So, when I choose UNITS here, you see these formulas update for UNITS, and then it was just a simple matter of creating the charts based on that data.
Insert, chart, and so on, that kind of all makes sense. To get the units and values, that's actually a textbox. I put the textbox in there. INSERT, SHAPES, TEXTBOX, and then in the formula bar, I typed an = sign and clicked on cell H2 to get that formula so that way it updates as well. [ =SHEET1!$H$2 ] So, a very nice cool trick that Mike Alexander showed me that makes people think that we're variably getting the data from here in the chart, but, in reality, we're using this helper range out to the right-hand side to solve the problem.
Well, hey. I want to thank Mike from Greece for sending that question in and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
