Learn Excel – UDF Rollover Jordan Goldmeier – Podcast 1813

Learn Excel – UDF Rollover Jordan Goldmeier – Podcast 1813


Learn Excel for MrExcel Podcast, Episode 1813:
Jordan Goldmeier, the Rollover Method. Bill Jelen: Hey, welcome back to MrExcel netcast,
I’m Bill Jelen. This is awesome, here in the studio, we have
the newest Excel MVP, Jordan Goldmeier, and he has awesome things to show. Jordan, how’s he? Jordan Goldmeier: Hey, Bill. Bill Jelen: Yeah, Jordan? Jordan Goldmeier: What does the Excel developer
put in their hair in the morning? Bill Jelen: I don’t know, what? Jordan Goldmeier: Sum product. Bill Jelen: Aah. [Laughs] That’s awesome. Already– already, this podcast– this episode
of MrExcel podcast is funnier than the other 1812 that we’ve done. That’s good. Jordan Goldmeier: Well, thanks, Bill, for
having me on. I’m just going to do some real quick information
about myself. As Bill showed on the cover, I’m writing a
book: Dashboards for Excel. It’s probably going to change its name a few
more times– I was thinking, “50 Shades of Excel,” I like that. Bill Jelen: [Laughs] That would be a great
title. Jordan Goldmeier: See? Look, I got another joke in. Bill Jelen: That’s good. Jordan Goldmeier: I also just want to do real
quick about me. I work for a company in Dayton, Ohio– the
Perduco Group, that’s P-E-R-D-U-C-O– and we do data analytics, healthcare, and we’re
really excited. We’re doing some new sports analytics. So we’ve actually been working with some sports
teams, doing scout scheduling and ranking players evaluation, stuff like that. So, if you’re interested in any of that, it’s
www.perducogroup.com. Bill Jelen: Alright, so, what do you have
today? Jordan Goldmeier: So, today, we’re going to
talk about, what I like to call, the Rollover Method. There’s really no official name for it– it’s
something that I sort of discovered, one dark and stormy night– but, what it essentially
allows you to do, is execute a macro when your mouse is hovering over a cell. So, for example, I made this Periodic Table
of Elements and, as you can see, when I roll my mouse over these different cells, information
changes about the different elements, and I can actually go over here on this periodic
table and I can click, and I can roll over, and you see that different element groups
are being highlighted. So, I can actually roll over, I can select
them all, if I want– just to give you a demonstration– and, by the way, this is all downloadable–
we’ll provide the link, http:optionexplicitvba.com/2012/06/- 21/interactive-periodic-table-of-elements-in-excel. So, we’re going to talk about how to do that,
and at the heart of this mechanism, is this Hyperlink formula up here. So, I’m going to start a new sheet, and I’m
going to show you how this is done. Now, what’s really interesting about this,
is that it actually violates one of the rules in Excel. So I’m going to teach you how to break one
of the rules– don’t tell Microsoft– but the main rule is that, user-defined functions
are not allowed to change the values of other cells. This has been the common thinking. In fact, it– as far as I can tell– it’s
appeared in every VBA book I’ve looked at, and we’re going to violate it. So, the first thing I’m going to do is open
up the Visual Basic Editor, and I’m going to create a new module, and, now, I’m going
to create a new public, user-defined function. If you’ve ever done this before, syntax is
pretty simple. I’m going to call this one, MyRollover (). So,
what I’m going to do in this, is I’m going to demonstrate it how we can violate the rules. And– let’s see, here– we were on Sheet1,
so I’m going to make this very easy. I’m going to say, sheet1.range, I’m going
to put (“A1”) in here, I’m going to set the Value to “Rule Broken!” –sheet1.Range(“A1″)
.Value=”Rule Broken!”. So, now I’m going to go back to our spreadsheet,
and I’m going to type in the formula that allows us to do this. So, remember that Hyperlink formula? We’re going to type in=HYPERLINK, and now,
here’s the trick: In here, I’m going to type (MyRollover()). So, instead of providing an address, a cell
address, a web address, I’m actually going to provide the UDF to it. So, for now, we’re going to leave the friendly_name
blank, I’m going to hit enter. Over here, as you can see, when I rolled my
mouse over– and I’ll just delete it here, just so we can see it again– I roll my mouse
over, it says “Rule Broken!” So, now we’ve actually violated a rule and,
in fact, Excel sort of has recognized that we violated that rule, because it gave us
this value error, and this is what it does when there’s an error. So, the question is, how do I get rid of that
pesky error? And the way to do that, is to put this IFERROR
around it. if I put this IFERROR around– and I’ve found,
I don’t know if this is necessary to put the same thing that you would put in the IFERROR
in the friendly text, but I’m going to do that just to keep things consistent. Hit Enter. We’ll resize this. As you can see, it works without– without–
resulting in an error, so let’s put this to good use. So, let’s use this in a real example. So, I’ve created a new sheet– Sheet3– and
what I’m going to show you how to do, is sort of create a Rollover on your own that’s going
to pick up– that’s going– that you can use and show different graphs. So we’re going to take something that was,
theoretically, “Cool, we broke the rules,” now, how can we use breaking the rules to
our advantage? So the way we’re going to– the way I’ve done
this here– is I’ve– you see my mouse is over North, it’s showing North, as I put it
over South, East, West, it updates. So, right away, you’ll notice that when my
mouse is over West– over this blank spot– nothing’s really happening, so I can act–
well, I can really show you– in East, or if I put it in South, nothing’s happening. So what I’ll do is, if you put Word Wrap on,
that will actually fix that– I don’t know why it fixes that, it was kind of a weird
discovery on my part, but it does fix that. So here’s how this mechanism works: When I
click on North, you see that I have this MyRollover and it’s putting the word North in, so it’s
actually taking what’s supposed to be there and it’s going to send it to the UDF. So, over here, you also see that we have this
table and then I have this graph here– it’s linked to, sort of, our current selection. So, as this cell here changes, it’s going
to do an HLOOKUP on our table over here, and it’s going to deposit it here, and then our
chart is linked here. So, how do we get this cell to change? And the way we do that is, it’s in our Rollover
method. So I’ve actually named this cell Current Region. And when I do my Rollover, I use this shorthand–
these brackets– so I say [CurrentRegion]=Region– which is the string passed in. And, as you can see from here, we’re actually
passing in that string. So, basically, passing the string tells it
which item to look up, that look up is deposited, and that informs the chart. So, that’s actually– that basic mechanism
is– how I was able to create that Periodic Table, and in fact, we can– you can– do
all sorts of things with this– you can do conditional formatting, so when your mouse
hovers over it, it will change color. There’s all sorts of things to do. Bill Jelen: Okay, so that is a radically,
cool method, and when you– when Jordan– set this to a few of the other MVPs, I don’t
think anyone had ever seen anything like this. A great way to break the rules in a very useful
and clever, clever way. Alright, so again, your book “Dashboards”
–and, for example, that’s coming out when? Jordan Goldmeier: Hopefully, February. Bill Jelen: February 2014. Jordan Goldmeier: That’s the goal, yep. Bill Jelen: Go out to Amazon right now and
Pre-order it, so, check that out. And, also, your website is? Jordan Goldmeier: Theperducogroup.com. Perduco, just in case you’re wondering– because
everyone always asks us that– it means, to deliver. So, it’s a Latin word and we were trying to
make a clever name about what we do, so– Bill Jelen: Clever for all of the people who
have taken Latin. Jordan Goldmeier: [Laughs] Yeah. So, check us out. If you like what we do, we have our contact
information at the bottom, you know, just stay in touch. Follow my blog– I also have a blog– optionexplicitvba.com. Bill Jelen: All right, that was an amazing
trick from Jordan. I want to thank Jordan for stopping by on
his way back to Dayton, Ohio. And I thank you for stopping by. We’ll see you next time for another netcast
from MrExcel.

18 thoughts on “Learn Excel – UDF Rollover Jordan Goldmeier – Podcast 1813

  1. Hi,
    How did you create this table for the chart that it's all changing when you click "north"?
    Also could I find anywhere this worksheet? Just to look how it works?

  2. I FOUND THE EXCEL FILE FOR THIS VIDEO… It is at https://github.com/Apress/dashboards-for-excel/blob/master/Chapter%205/Periodic%20Table.xlsm
    Just click the download.

  3. Unbelievable!!!! Congrats for this. I'm gonna do a post in my Brazilian blog about this, indicating this video and your website. My site is www.guiadoexcel.com.br

    Can I do it?

    []s Rieper

  4. VC PODERIA ME AJUDA! EU SÓ QUERIA passa o malse por sima se uma celular e ela mudar de cor… coisa simples eu vir aquela sua tabela ela e muito boa, exemplo na eu tenho 5 celular com o nome DINELSON, RENAN RENATO EMILLY. au passa o malse por cima ela muda de cor, se vc me poder me ajuda eu agradeço abraço!

  5. Could you help me? I ONLY WANTED to pass the evil if by sima if a cell and it changes of color … simple cell with the name of her DINELSON, RENAN RENATO EMILLY. The Mutating Changes The Mutating Changes The Mutations Of Color, If You Love Me Help Me Change The Mutations Hugs!

Leave a Reply

Your email address will not be published. Required fields are marked *