Excel in Depth 34 - Dynamic SmartArt
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel In Depth chapter 34 - SmartArt to Shapes!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well you know, all the time we keep track of things in Excel, right now I'm keeping track of these videos I'm creating, here's all the videos I have to do, I keep track of which ones are done, and which ones aren't.
So, then I create a couple of formulas over here, figure out how many are done, just out of the column, how many do I still have to go, that's the COUNTA of all the texts over here minus how many are done, the percentage that are done, and then a concatenation formula here, I like this one.
Take that 57 with some text, and then format the percentage in parentheses, so you know, I get this little status of how things are going, and that works.
But maybe I want something to motivate me a little bit more, maybe you have a nice message to communicate.
So I'm going to go to insert SmartArt, and let's go List charts or Relationship charts.
Ah, here we go.
Let's go with the arrow ribbon, click OK.
Alright, now what I really want to have is, I want to have those formulas appearing here in this market.
Unfortunately SmartArt was built for PowerPoint, and they never thought about having dynamic values, so it just doesn't do it.
Here's what I want to do, I want you to build the SmartArt with just some dummy text, so "NN videos done (nn%)", and then over here "nn videos to go".
Alright, and what you're trying to do there is, you're trying to just put in dummy text of about the right size.
Alright, then do any formatting you want to do, really important that you get this thing formatted perfectly the way that you want it.
Choose a style here, I'll go with uh, sure yeah, that one, alright.
So we get things exactly like we want them.
And then here's the trick, they actually added this to Excel 2010 I think, because I complained about it in Excel 2007.
So I said "You really need a way that we can make this dynamic." And I hassle the people at Microsoft, I said "AutoShapes have been able to have dynamic formulas for ten years, why couldn't you hook this up?" They said "Oh, it's more complicated than that." But they did show me a way to convert SmartArt to shapes!
Alright, so we have this SmartArt, I'm going to choose a SmartArt, and then here, they actually give us an icon now, rather than the old trick, Convert to Shapes, and now those are shapes.
And now that we have shapes, see, we have a text box there, and I want that text not to be static, but I wanted to come from a formula on the sheet.
So I click up here in the formula bar and type =E5, press Enter.
Alright, and check that out, now that text in the shape is coming from the formula.
Alright, over here =E6, Enter.
And then how it works, I'm going to come down here, this is chapter 32, this is chapter 34.
So I enter a 1 there to indicate that this video is done... (silence) Alright, and then how it works is, if you update your model, we'll just enter some 1's here, you see that here- Well, it's not SmartArt anymore, but it was built using SmartArt, automatically updates to show the new values, so very cool to have dynamic SmartArt.
Microsoft doesn't let you do it, but they very nicely added the Convert to Shapes, so that way they just become very odd shapes(?), you can add cells that contain formulas as the source of all of those shapes.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Excel In Depth chapter 34 - SmartArt to Shapes!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well you know, all the time we keep track of things in Excel, right now I'm keeping track of these videos I'm creating, here's all the videos I have to do, I keep track of which ones are done, and which ones aren't.
So, then I create a couple of formulas over here, figure out how many are done, just out of the column, how many do I still have to go, that's the COUNTA of all the texts over here minus how many are done, the percentage that are done, and then a concatenation formula here, I like this one.
Take that 57 with some text, and then format the percentage in parentheses, so you know, I get this little status of how things are going, and that works.
But maybe I want something to motivate me a little bit more, maybe you have a nice message to communicate.
So I'm going to go to insert SmartArt, and let's go List charts or Relationship charts.
Ah, here we go.
Let's go with the arrow ribbon, click OK.
Alright, now what I really want to have is, I want to have those formulas appearing here in this market.
Unfortunately SmartArt was built for PowerPoint, and they never thought about having dynamic values, so it just doesn't do it.
Here's what I want to do, I want you to build the SmartArt with just some dummy text, so "NN videos done (nn%)", and then over here "nn videos to go".
Alright, and what you're trying to do there is, you're trying to just put in dummy text of about the right size.
Alright, then do any formatting you want to do, really important that you get this thing formatted perfectly the way that you want it.
Choose a style here, I'll go with uh, sure yeah, that one, alright.
So we get things exactly like we want them.
And then here's the trick, they actually added this to Excel 2010 I think, because I complained about it in Excel 2007.
So I said "You really need a way that we can make this dynamic." And I hassle the people at Microsoft, I said "AutoShapes have been able to have dynamic formulas for ten years, why couldn't you hook this up?" They said "Oh, it's more complicated than that." But they did show me a way to convert SmartArt to shapes!
Alright, so we have this SmartArt, I'm going to choose a SmartArt, and then here, they actually give us an icon now, rather than the old trick, Convert to Shapes, and now those are shapes.
And now that we have shapes, see, we have a text box there, and I want that text not to be static, but I wanted to come from a formula on the sheet.
So I click up here in the formula bar and type =E5, press Enter.
Alright, and check that out, now that text in the shape is coming from the formula.
Alright, over here =E6, Enter.
And then how it works, I'm going to come down here, this is chapter 32, this is chapter 34.
So I enter a 1 there to indicate that this video is done... (silence) Alright, and then how it works is, if you update your model, we'll just enter some 1's here, you see that here- Well, it's not SmartArt anymore, but it was built using SmartArt, automatically updates to show the new values, so very cool to have dynamic SmartArt.
Microsoft doesn't let you do it, but they very nicely added the Convert to Shapes, so that way they just become very odd shapes(?), you can add cells that contain formulas as the source of all of those shapes.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
