Howdy folks. Jeff Weir here. The subject of today’s post is “What would James Bond have in his Personal Macro Workbook”. The Personal Macro Workbook is a secret place where you can save frequently used macros, automation snippets and code modules so that you can call them from any file and save tons of time, not to mention look dashingly good in-front of your boss. In this article, we’ll take a look at how to set up a personal macro workbook and use it.
The other day, undercover Excel secret agent KV came up a great list of interview questions in response to Chandoo’s great article What are best Excel interview questions?
I particularly liked this one:
Do you have a Personal Macro Workbook setup ? If yes, what are the most common macros you use in your daily work?
Secret Agent KV obviously has some purpose-built gadgets stored in his Personal Macro Workbook that he employs to kill inefficiencies. And he obviously also knows the two top-secret reasons to keep code in a Personal Macro Workbook:
- To keep it safe from international spy rings.
- So you can whip it out whenever you are cornered by any evil-looking spreadsheet.
While that first point is important, that second is the clincher: code stored in a personal workbook can be unleashed with a simple judo-chop to the throat. Err, I mean a simple Alt + F8 to the keyboard:
And the best thing of all: this top secret code can be recycled over and over again, just like a James Bond plot!
But I don’t know how to write code… I hear you say. (I’ve bugged your office). Well…does James Bond build his own top-secret gadgets? No, of course not. He has Q to do it for him:
Okay, okay…so you don’t have Q at your disposal. But you do have G:
Google. Because the secret code for world Excel domination has been leaked all over the internet, by the likes of secret agent KV and 10,000 other spooks just like him. Many strains of this code are highly lethal to just about any kind of inefficiency you could ever think of. You just need to cut and paste it into your Personal Macro Workbook and you’re on your way to world Excel Domination.
So in the international war on spreadsheet terror, KV’s on to something. Which is why I asked him if to digital pen to digital paper, and share some more of his ill-gotten top secret code with us here on ExcelWikiLeaks.
Unfortunately for us, he said no. So I told him that unless he complies by this time tomorrow, I’ll publish his real name. So he said yes. (Khushnood Viccaji is obviously a man with something to hide. But don’t worry, Khushnood …your secret identity is safe with me.) So tune in tomorrow to see secret spy code in action.
Meanwhile, I’ll set the scene by showing you where and how to stash your to secret code. And after that, I’ll point you to some code I’ve only just completed that could well save you hours if you use Pivots a lot. So read right to the end, good spooks.
What exactly is a Personal Macro Workbook?
Often on the internet you’ll find workbooks that you can download that contain useful inefficiency-killing code. But those macros are firmly attached to the workbooks they live in. So as long as you have those workbooks open, you can use those macros. But as soon as you close those workbooks, those macros are no longer available.
But just as secret agents have briefcases with false bottoms (and possibly girls with false body-parts), Excel has a hidden workbook – the Personal Macro Workbook – that gets opened automatically any time you start Excel.
So just like James Bond’s hidden gadgets, any code you stash in there is constantly at your command, just waiting to be whipped out and triggered at the slightest provocation. (Which reminds me of another aspect of Mr Bond, but we’ll keep this family friendly for now, eh?)
How do we set up our Personal Macro Workbook?
Note that the Personal Macro Workbook doesn’t exist until we instruct Excel to create it. But that’s simple. If you’ve got Excel 2010, here’s how. (If you’ve got a different version, you may need to do some secret squirrel industrial espionage via Google to get the secret plans for your particular version)
Step One: Make sure the Developer Ribbon has been enabled.
This isn’t strictly necessary, but we’ll go ahead and do it anyway. Have a look at the top of the workbook. Do you have a menu option ‘Developer’ showing?
Yes? Great.
No? No problem, let’s sort that now. Click File, and select the Options option:
Now click Customize Ribbon, and then check the Developer checkbox on the right hand side, then press OK:
Cool, now that Developer option should show in the ribbon.
Step two: Record a ‘dummy’ macro into your Personal Macro Workbook
To create our Personal Macro Workbook, we simply record a macro and tell Excel to put the macro inside it. Excel then realizes “Well, duh…I better create a Personal Macro Workbook first, because it doesn’t actually exist yet”.
To do this, click on the Developer option in the ribbon, which brings up a whole heap of fancy looking options. (Don’t worry, even James Bond doesn’t know what most of these do).
Now click the Record Macro option that shows in the left hand side of the Developer ribbon:
Note that you could also click the exact same icon that you’ll find down the bottom left hand corner of the spreadsheet:
Either way, this brinks up the Record Macro dialog box:
By default, Excel tells you that it will store the macro it’s about to record in This Workbook. But that’s not what we want this time. Instead, we want Excel to store the macro in our Personal Macro Workbook, which will force Excel to create the Personal Macro Workbook.
So change that “Store Macro In” drop-down to this:
Now go ahead and push OK.
Now…I don’t want you to panic or freak out or anything… so promise me you’ll listen very calmly to what I’m about to say. Promise? Okay, good. The Macro Recorder is now recording everything you do. So try not to make any sudden moves or pull any funny faces. Don’t pick your nose. And please, don’t make any loud body noises. Because it’s all going down on tape. Just act normal. (Unless of course you routinely pull funny faces while picking your nose to the tune of loud bodily noises. In which case, give me you best abnormal.) That’s it.
Okay…now we want to carefully stop recording. Why? Because the purpose of this exercise is not to actually create magnificent code, but rather to create a secret compartment within Excel in which to store magnificent code. And merely recording a completely blank macro like we just did is enough to get Excel to create our top secret Personal Macro Workbook.
To shut this thing down, I need you to s-l-o-w-l-y reach into your spy satchel and pull out the red-handled, thin-nosed wire cutters. Now, c-a-r-e-f-u-l-l-y unscrew the bomb housing, and locate the detonator. See the blue wire sticking out of the detonator? C-a-r-e-f-u-l-l-y cut it before the timer goes to Zero.
No, wait a minute…I think it’s the red wire.
NO, WAIT…THAT’S NOT IT EITHER. Umm…ahh…err….Ah…quick…Jeff…think…argggh…Oh No…Mummy!
WAIT, I REMEMBER NOW! To shut this thing down, push the STOP RECORDING buttons in the ribbon:
Alternately, you can click the Stop Recording icon that you’ll find down the bottom left hand corner of the spreadsheet:
Phew, that was close. Sorry about that. Hey, well done, you! Believe it or not, just by setting the macro recorder to Record and then clicking Stop forced Excel to create your very own Personal Macro Workbook. Who said giving birth was hard, eh! So where is this new baby? Well, duh…she’s hidden. I mean, really! Are you fit to be a parent, with an attention span like that?
Alright, alright, if you really want me to prove this immaculate conception, then click View from the top of the ribbon, and then click Unhide:
BEHOLD! A secret hidden workbook called PERSONAL!. Just waiting to be filled with your secret stash of code.
Isn’t she a beautiful baby? Look, she has your father’s eyes! (Don’t bother pushing OK to unhide her…this was just to prove that she exits.)
Step three: Put some re-usable code in there
The easiest way to get code into your Personal Macro Workbook is via something called the Visual Basic Editor, or VBE for short. You can open the VBE by pushing Alt + F11 from Excel. (Pushing Alt + F11 again takes you back to Excel). Go ahead, give it a try. You’ll see something like this:
Scary, isn’t it. It doesn’t look anything like the Excel that we know and love. In fact, it’s about as attractive as a villain from a Bond film. But don’t worry, we’ll defeat this bad boy together.
(Note that depending on your settings, you might see a few more windows than what the above screenshot shows. So just ignore any of the below steps that aren’t relevant.)
The first thing we want to do is to bring up something called the Project Explorer window, which is a tree-map view of all the different workbooks and Add-Ins that you currently have loaded. To make it visible, select Project Explorer from the View tab:
…which will bring up this:
And look…we can already see our new baby listed on the side: Personal.XLSB. This being a spy flick and all, Excel gave our new Personal Macro Workbook the code name XLSB. (Note that if your version of Excel is a little older, you will see Personal.XLS instead. Basically you have the Sean Connery bond, and not the Danial Craig revision. But they are both deadly.)
What we need to do is expand that Personal.XLSB node by clicking on the + sign to the left of it. Here’s what the first level looks like:
So now we see a folder called Microsoft Excel Objects and another one called Modules. The Microsoft Excel Objects node expands out to show us two further nodes called Sheet1 and ThisWorkbook:
…but we don’t use these, so just ignore them. What we want is to expand the Modules node:
Now we just need to double-click on that Module1 thing to bring up the Code Window associated with this module. You might already see a code window, or even a whole bunch of them floating on top of each other. Double-clicking on a particular Module will bring the applicable code window to the top.
And look, there’s that Macro we recorded earlier. That’s not so scary is it…now we know where code turns up when recorded to the Personal Macro Workbook – in a Module. Any time you record a new macro, Excel sticks it in a Module. If you’ve already been recording Macros during your current Excel session, then Excel sticks it in the same Module. If you start a new session (e.g. close down Excel, then restart it and record another Macro) then Excel would put the code you record in a new module. And any time you put new code into a Module – either by using the Macro Recorder or by cutting and pasting code directly in to a Module, then when you close Excel it will prompt you if you want to save changes to your Personal Macro Workbook.
It doesn’t really matter which of the Modules you store your code in in that Personal Macro Workbook – just so long as each routine has a different name. In fact, you can have just one module in there if you like that contains hundreds of different routines. I have several modules, that I’ve given names like modPivotTables and modText and modCharts, with each module containing routines related to the name of the module. I find this makes it easier to track down where a particular routine is stored, in case I want to make changes to it in the future. You can rename Modules by bringing up the Properties window. To do this, select view from the Toolbar, and then click Properties Window:
…which will bring up a new window that will probably sit right below the Project Explorer window by default:
If it’s floating somewhere else, just double-click it and it will then ‘dock’ below the Project Explorer window as shown above. To change the name of a Module, just type the new name where I’ve indicated above.
So now we know where to copy new code, if we want to store it there for reuse. Sweet! So let’s delete that code, and put something useful in there right away, shall we?
So what other kinds of things might you want to put in there?
Well, efficiency comes in all shapes and sizes. I’ve got macros in there that are just a few lines long, and that save me a mere two or three mouse-clicks, like this one:
Sub ClearFormats()
Selection.ClearFormats
End Sub
All that does, is clear the formats from the selected range. In fact, it does exactly the same thing as this does:
...the only difference is that I assign this to a keyboard shortcut of [Ctrl] + [C] so that I don't even have to take my hands off the keyboard to launch it. (Sure, I could use Excel's shortcut of [Alt]+[H]+[E]+[F]. But that's more random digits than my memory or fingers can usually handle.)
Assigning a keyboard shortcut to launch a Macro is easy. First, we switch from the VBE back to Excel using [Alt]+[F11], and then we push [Alt] + [F8] to bring up the Macro Dialog Box:
Then we click the Options button on the bottom right, which brings up the Macro Options dialog box, where we can enter in the key combination we want to use:
I usually use a combination with [Shift] in it, because this stops me overwriting any of Excel's existing keyboard shortcuts.
Can I put clever home-made Worksheet Functions in there too?
You bet. I often store custom-made functions - called User Defined Functions - in my Personal Macro Workbook that can be used within a formula in a cell, without having to even launch the Run Macro dialog box.
For example, say you have some delimited text in a column like so:
Let's say you wanted to extract the 3rd item in that list, but for some reason can't use Text-To-Columns because you don't want to blow your Excel Ninja cover. You'd need a pretty complicated formula to do this. Probably something like this:
=MID(A2,FIND("|",SUBSTITUTE(A2,";","|",2))+1,FIND("|", SUBSTITUTE(A2,";","|",3))-FIND("|",SUBSTITUTE(A2,";","|",2))-1)
That formula is a pain to remember how to put together, not to mention a drag to write. So instead, you could just store the following function in your Personal Macro Workbook:
Function SplitText(str As String, strDelimiter As String, lngOccurance As Long)
Dim varArray As Variant
varArray = Split(Expression:=str, delimiter:=strDelimiter)
SplitText = varArray(lngOccurance - 1)
End Function
Then you could use it in any workbook by using this formula:
=PERSONAL.XLSB!SplitText(A2,";",3)
...for exactly the same effect. But with much less effort.
Note that we have to prefix the formula with PERSONAL.XSLB! so that Excel knows where to find the code behind this puppy.
If you send the workbook to someone else that doesn't have the same Macro in their Personal Macro Workbook, then they will just get a #NAME? Error. To avoid this, you could send them the Macro itself and point them to this article. Or you could copy the code from your Personal Macro Workbook and paste it into a Code Module in the Workbook you are sending. To do this, select the workbook of interest in the VBE, then click INSERT and select Module from the menu, as shown below.
This will put a blank Code Module in the workbook itself, and then you can copy the in the workbook that you are sending out, and then copy the macro from your Personal Macro Workbook into that empty Module:
...and then ditch the =PERSONAL.XLSB bit in your formula:
=ExtractElement(A2,3,";")
Those are fairly trivial, aren't they? What about a really intricate gadget?
At the other extreme, I've got macros in there that I've worked on for months, that save me hours if not days of work. And some let me do the downright impossible.
Here's an example: Imagine you've got a list of 1000 Top Secret Passwords (left) and a PivotTable of 20,000 Top Secret Passwords (right). And imagine that in order to save the world, you need to filter the PivotTable on the right so that it exactly matches the list on the left. Quickly.
How long do you think it's going to take you to filter that pivot using this:
Hours? Days? What if the fate of the free world depends on you getting it right? Worried you might make mistakes? Would you even notice? Would you bother checking? How will you even do this...as that PivotFilter Dialog box says, Not All Items Showing! So we can't even see all items to filter them. Goodbye free world!
Enter my FilterPivot macro, that lives in my Personal Macro Workbook. It's just itching for a fight like this one. Let's judo-chop the keyboard with Alt + F8, bring up the Run Macro dialog box, and see it in action, shall we?
Look, there it is. All I need to do now is select it, push Run, and follow the instructions:
*BING*...Done! See - the PivotTable on the right is filtered so that it exactly matches the list on the left.
How's that. Neat, eh! This code is really, really fast under a range of different scenarios. It works out whether it's quickest to:
- make all items visible in the pivot, and then hide just the PivotItems that don't match the filter terms; or
- hide all but one items in the PivotField, and unhide just the PivotItems that do match the filter terms
If you've got a PivotTable with 20,000 items in it, then here's how long it takes:
- Filter on 100 search terms: 7 seconds
- Filter on 10,000 search terms: 1 minute, 30 seconds
- Filter on 19,900 search terms: 5 seconds
Want one of your very own? Go and visit my recent post Filtering Pivots Based On External Ranges over at the Daily Dose of Excel blog, and cut and paste away. Note that this code requires Excel 2010 or later...and you want to scroll to the end of the acticle and look for the code under the heading Faster Approach. If you've got 2007 or earlier, all is not lost: look for the code listed under the Slower Approach heading. It's not as fast as the times I've posted above, but still a heck of a lot faster than doing it my hand.
That's the beauty of the web - there's weapons-grade code just waiting for you to steal. So again, you don't have to know how to write code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste. Heck, even Austin Powers can do that without screwing it up!
That's more than enough for today. Tune in tomorrow, and we'll see what kind of time-savers Secret Agent KV has in his Personal Macro Workbook.
And if you know of amazing routines published on the web somewhere that are Personal Macro Workbook ready, then let us know about them in the comments below. But only if these routines are ready to go, and are the best of the best. Like the code at these two links:
http://peltiertech.com/WordPress/label-last-point-for-excel-2007/
http://datapigtechnologies.com/blog/index.php/auto-format-pivottables-to-match-source-data/
About the Author.
Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:
=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))
That’s right, pure #VALUE!
Find out more at http:www.heavydutydecisions.co.nz
205 Responses to “Interactive Dashboard in Excel using Hyperlinks”
Dear Team, this is what I was excatly looking for! Thank you so much for such a cool blog!
I think this is the most awesome site on Excel! Best regards - Aniket
Chandoo, another inspiring post. Thanks.
I dig. I really do. One of the things I don't like about the dashboards I do is that the UI can be pretty 'bleh'. Tricks like this give me more options to make the UI work really well.
Okay, that's just straight-up awesome!
A cool little trick that delivers some excellent results. Easy to do and especially easy to adapt for your own needs.... it doesn't need to be charts, you can use it to highlight columns too! So i can now scan data in tables so much easier... great!
Hey, this looks familiar lol. Just a little extra thing to add: your function will continuously write to the named range so long as your mouse hovers over the hyperlink. But if you place this in your code:
.
If Range("valSelOption") = seriesName.Value Then
Exit Function
Else
Range("valSelOption") = seriesName.Value
End If
.
You should notice a slight speed improvement. If you don't notice it now, you will notice it when there are several more hotspots.
Is it possible to add a second series in this chart?
Hey Jordan, I noticed the difference. I was a bit concerned about the speed as I am usually an impatient person.
Thanks Chandoo for posting this. I was using the dropdown list but this is more convenient.
Very cool...I'll have to try this!
Hi
Just wondering if there is a excel 2003 workbook version available for this?
Thanks
I'm having problems downloading your file. Is it 2003 compatible?
Rich,
You can open it in 2003 (assuming you can normally convert other 2007 files).
Just rename the file extension to .xlsm and it should work.
I love this interactive dashbaord and I'm looking to implement on a few sheets wheere I normally apply filters as a drop down choice. Thanks for sharing.
I downloaded the file and opened it with a converter to 2003 and the page was blank.
Rich I think you and I are having the same issue. I am wondering if this will only work in 2007 and up.
@Rich You need Excel 2007 or above as the file has .xlsm extension.
ok, Chandoo, did you correct the link? when i go to open file, it shows .xls not .xlsm
That. Is. Awesome.
Chandoo: Can you simlify step 4. 'm battling to get it right.
This is awesome!
I am seriously struggling with step 4, how to get the hyperlink to worK.
Please help, i followed all instructions and then it says "Cannot open the specified file"
Dear Chandoo
thank you, Its really awesome, this could add more value to the dashboard.... its is really going to help me lot... thank you once again.
We expect more from Chandoo,,, with biz schedule I always find a time to visit chandoo, as I have trust that, Chandoo will come up with awesome ideas always.
Guys: If you manage to get step 4 to work, please post the easiest steps to follow.
Please guys.
Chandoo, this is wasome, I always smile when I see a new post in my inbox.
Love the dashboard!
Personally not a fan of array-formula's, so i removed those.
Guys
Can someone please post the steps to execute STEP 4 on Chandoo procedure.
I can't get the Hyperlink to work. Please guys, someone assist with this.
Hey guys, if you get an "Cannot open the specified file" error, ensure the following:
1. Within the first argument of the Hyperlink formula you've spelled the name of your UDF correctly.
2. The first argument is NOT in quotes. Correct: HYPERLINK(highlightSeries(B3)),"6"); INCORRECT: HYPERLINK("highlightSeries(B3))","6")
3. Your function is specified as PUBLIC and is in a module!
I love the dashboard! I follow all the steps and check the formula I'm still getting the "Cannot open the specified file" error.
I am not getting any value for the "valSelOption" cell either.
Did I miss something?
I've got the same problem. I copied "highlightSeries" from macros and pasted it to cells with arrows and it works! Keep trying
Hello Chandoo
I learn a lot from you.
Thanks for sharing your expert knowlege.
You are my excel guru.
Best
Thanks Jordan Goldmeier. I've been waiting patiently for someone to post. My mistake was on step 3, It got after I put it in module.
Thanks Jordan Goldmeier, I've been waiting patiently for the help. Mys mistake was on step 3 on your explanation.
Thanks once again.
Helloo Mr. Chando!!!
It'z awesome! Can it be linked to web page with all it's interfaces! I've found a challenge in mrexcel and it'll be too good for them.
Another excel colleckal.
Me likes it a lot!!! Going to try in in a dashboard I'm working as we speak 😀
Is something in Step 1? How is valSelOption important? Where are you using it?
You lost me at "magic" 🙁
"Lets call the series name in fifth column as “valSelOption“. Lets assume that we will use some sort of magic to change the series name.
Note: Using this series name, we can fetch the position of the series out of 4 with MATCH formula. Once you know the position, You can fetch corresponding values using INDEX() formula."
Is something missing in Step 1? How is 'valSelOption' important? Where are you using it?
You lost me at "magic" 🙁
"Lets call the series name in fifth column as “valSelOption“. Lets assume that we will use some sort of magic to change the series name.
Note: Using this series name, we can fetch the position of the series out of 4 with MATCH formula. Once you know the position, You can fetch corresponding values using INDEX() formula."
Thanks Jordan!
Works a charm!
@Chandoo. Here's my idea: users can roll over a graph (which has a hyperlinked cell underneath) and the camera tool "zooms" in on it.
Take a look: http://kvisit.com/Ssoq3AQ
@All... if you have trouble understanding this tutorial, see this video: http://www.youtube.com/watch?v=T7eDQqPZOm8&feature=channel_video_title
@Jordan: Very cool indeed. The camera tool based zoom.
I may have missed it, but how do you disallow scrolling by the user. That's a good thing because it makes it makes it work more like an application. I just don't know how to do it.
Thanks Chandoo for taking time and creating a video demonstration. You really go out of your to help your followers. Thanks again.
@Jordan: Keep it up.
I thought this was amazing.. At work, I used the same concept and it works wonderful !
Gr8 going Chandoo.. Goes to say Excel is an ocean and provided we use it right, we can achieve the results we want.
@Bobby Bluford
This is a creative use of the Freeze Panes option found in the Views menu.
Is there a reason the array formula was used. I don't think it's necessary, right?
Very nice, Chandoo! I think this technique is amazing! Thank you!
Message génial, il m'a vraiment aidé beaucoup.
[...] days ago, we published an article on how to create interactive dashboards using hyperlinks. Many of you loved this tutorial. But quite a few of you also said, this tutorial is a bit complex [...]
What about those of us stuck with Excel 2003. Any options? (We will upgrade someday. But, just when that day will come is an open question . . .) Thanks.
Brilliant!
Inspiring...now I wanna change like a 100 reports.
One of the best Excel-related items I have seem in a long time.
Many, many thanks.
Hi Chandoo:
Sweet!
Been playing around with it. But it appears that the charts have to tracking the same thing, such as numbers. I tried to create one of the graphs as a percentage, but the Y axis then for some reason changes in the other charts. Any idea why this?
@Russ
It is the same chart in all 4 cases,
It is the data that is being changed behind the scenes by the process.
@All, Russ
I have uploaded a small modification to Chandoo's original file which allows for the display of different chart types and even pictures.
You can find it here: http://chandoo.org/wp/wp-content/uploads/2011/07/rollover-hyperlink-with-Pics-demo.xlsm
It uses the same Camera Tool with a linked formula using Indirect technique to retrieve different Charts, Refer http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/
Enjoy
Hui...
[...] 7. Crea un dashboard interactivo usando hiperlinks [...]
[...] chandoo??????????????,?hyperlink??excel??????????. [...]
Hi Chandoo / other Excel MVP's
I have used this in Excel 2003 and it works fine, I have a second table and chart to add this to within the same workbook, I have used cell name valSelOption 2 and highlightseries2
For Example:-
=IF(ISERROR(HYPERLINK(HIGHLIGHTSERIES2(E47))),"6",HYPERLINK(HIGHLIGHTSERIES2(E47)))
I can not get this to work, can anyone see what I am doing wrong?
Best regards
Ajay
@Ajay
The VBA associated with this workbook is only setup to return a value associated with 1 range/area
Can you post/email your file and I'll look at a work around
[...] link. [...]
Hi! Jordan--when I put in your code---
If Range(“valSelOption”) = seriesName.Value Then
Exit Function
Else
Range(“valSelOption”) = seriesName.Value
End If
Everything breaks. Actually, whenever I change the function from anything but what is there, the hover over and highlights series function doesn't seem to work. Any advice? Thanks.
Hi !
How do you make this work if you have to protect the sheet ?
I just built this an it works great.... I am hoping for some additional help though. On the one I built, my second column is gross margin %. Is there away for the graph to adjust the format of the numbers on the left access to whichever type of data is selected? So if I hover over sales, the format is % but when I move over gross margin %, it changes the format to %? As it is, my gross margins are all less than $1.00
@Dustin
Have a read of my comment 46)
Thanks Hui... I did consider that but didn't take it all the way to completion. Based on your workbook, it looked like the pictures were not as clear as the actual graphs. Is that just me or are you seeing the same thing? Is there a way to avoid a lower quality graph?
@Dustin,
That's is a limitation using this technique
I am not sure if this has been asked but at work they have excel 2003.
Is there a 2003 version, can I do this in 2003?
Unfortunately I can only use what work provides.
@Zilla37
Can you try this: http://chandoo.org/wp/wp-content/uploads/2011/07/Rollover-hyperlink-demo-97.xls
Please let us know if it works or not.
I have the same problem -
i am running excel 2003 and the rollovers work great for a few minutes
then the down arrow is replaced by a bunch of webding characters, and the spreadsheet flickers....seems there is an error with the
hyperlink
formula perhaps?
also - the webdings character 6 doesn't look like a down arrow from what my version shows.
Please reply to my email as well.
Steve
[...] Interactive Dashboard in Excel using Hyperlinks. Chandoo [...]
Hi Hui
Yes the link works except for one thing.
The down arrow for the YTD (webdings size 11 ) doesn't appear. What shows is several webding icons not just the single down arrow. I think its picking up all the text from the formula. Is there a way to correct this?
Thanks Chandoo. Excellent stuff and easy tutorial.
Thanks for this trick .. but i faced a problem when i was using this ....
I have below mentioned formulas in my excel sheet .. previously it used to work properly .. link to the correct cell & value in the cell How!U12 used display.
=HYPERLINK("#" & CELL("address",How!U12),How!U12)
but after implementing the above trick in my worksheet .... now its displaying
"#'[R17954_System Testing Testlog vGraphs.xlsm]How'!$U$12 " instead of the value in the cell How!U12
Please help me if im doing anything wrong ...
But if i go to formula bar & Hit Enter without changing anything.. its working fine ...
I have saved the example workbook which works perfectly - however, I tried to recreate this effect myself in my own spreadsheet simply to see if I could do it myself. I have checked every formulae/function and named range and everything 'appears' to be an exact replica of the example workbook but the only thing that does not work is the hyperlink, when I hover my mouse over any of the hyperlinks nothing changes. When I set up the chart, it accurately reflects the initial value(s) of 4 in the MATCH cell (i.e. it accurately picks up the values of the 'No. of Customers' column), but it never updates when I hover my mouse over any of the other hyperlinks.
My "MATCH" formulae cell is in O15 with value =MATCH(valSelOption,$K$16:$N$16,0)
As you can probably tell from the formulae above, my column headers are in range K16:N16
My data range is in K17:N28 and the array formulae is set in range O17:N28 thusly {=INDEX($K$17:$N$28,,$O$15)}
My chart header range is B3:E3 and my hyperlink formulae is set in range B6:E6 like so =IFERROR(HYPERLINK(highlightSeries(B3)),"6") (obviously, the reference to B3 in the hyperlink code is incremented accordingly across to E3)
I have checked my function code and it is appopriately coded as in the Chandoo's article (straight copy & paste). But nothing happens when I hover the mouse over the hyperlink cells. When I manually change the number in the MATCH cell (O16) the data in range O17:O28 updates accordingly but the chart does not.
Any ideas? Am I missing something REALLY obvious?
Cheers for any help or advice in advance!
Did you change the Named Formula ValSelOption ?
='Interactive Dashboard'!$AC$18
to reflect your data
Thanks for your help Hui - yes I did change the named range valSelOption, although in my case I changed it to;-
='Interactive Dashboard'!$O$16 - Because my named range is O16
This seems to be picking up the correct value from the value returned by the 'MATCH' formulae (in this case a value of 4 = "No. of Customers", however this value does not update when I hover over any of the hyperlinks. If I change this value manually (to say 3) the chart and the values in my array range (O17:O28) change accordingly, but the conditional formatting does not change and the value of the named range in O16 does not update to "Profits".
Does this make sense? Hopefully I have explained this ok?
Many thanks
Did you copy the VBA code from the worksheet object in VBA
It is only 3 lines?
Public Function highlightSeries(seriesName As Range)Range(“valSelOption”) = seriesName.Value
End Function
Yes I did, exactly as it appears in Chandoo's article above - completely unedited.
OK, thanks hui - the email should be winging its merry way to you now! Cheers
@Alex
In your UDF retype the 2 " around valSelOption yourself using the keyboard Shift '
Don't copy/paste from here
Voila
Notice that the Chandoo's Blog changes the " to something that looks like a " but isn't
THIS!!! OH MAN!!! I've been working this problem for hours!!! Thank you so much!!!
Aha! Thanks very much Hui! Works like a charm now - I was scratching my head wondering where I had gone wrong but could not see any obvious errors. Who'da thunk it would be something as simple as that!
Cheers for your help
Best regards
Alex
Hello Chandoo, this post is awesome !!
but I've a problem. I'd like to insert 2 differents graph with the same technique, how can I do it ?
@Shakeel
The chart is a simple chart looking up some data over in X17:AC32
This data is connected to Data in Col AC via an index which retrieves data into column AC
The index uses AC17 as the offset value when retrieving the data
You can have any number of charts all looking up different data but using the offset Value from AC17
Have a look at the same chart as above with a second set of data connected
https://rapidshare.com/files/3221111701/Rollover_hyperlink_demo_Hui.xlsm
Hi Hui
I like the before and after graph.
Is there anyway to have the previous and new numbers side by side in the same graph?
Hi
Is there any way to use this technique to filter a Pivot Table that then feeds a chart. I've tried extending the sub to include PivotItems.visible methods etc, but excel doesn't play ball.
Any ideas?
Did you ever figure out how to use this with a pivot table?
I have been having fun with this little trick for a while now and it's been working lvoely, however I have been experiencing the same problem as post 59 above;
============================================
59) r3n September 7, 2011
Thanks for this trick .. but i faced a problem when i was using this ….
I have below mentioned formulas in my excel sheet .. previously it used to work properly .. link to the correct cell & value in the cell How!U12 used display.
=HYPERLINK(“#” & CELL(“address”,How!U12),How!U12)
but after implementing the above trick in my worksheet …. now its displaying
“#’[R17954_System Testing Testlog vGraphs.xlsm]How’!$U$12 ” instead of the value in the cell How!U12
Please help me if im doing anything wrong …
60) r3n September 7, 2011
But if i go to formula bar & Hit Enter without changing anything.. its working fine …
========================================================
To reiterate, I have a hyperlink formulae in my worksheet which hyperlinks to another worksheet cell, based on cell value in cell "O1".
For example; Cell "O1" has a formulae which returns the value "[QDC Summary 11-12.xlsb]''!$A$1" (variable depending on item being viewed) and I have a hyperlink referencing this cell =HYPERLINK(O1,"Edit").
Now, the formulae and hyperlink work great, however, whenever I hover my mouse over the tabs triggering the highlightSeries/valSelectOption, these hyperlinks keep changing the "friendly name" in the hyperlink (in this case "Edit") to the hyperlink value of "[QDC Summary 11-12.xlsb]''!$A$1".
Although the hyperlink still functions perfectly fine, it is annoying have to keep activating the cell (F2) to set the "friendly name" back to "Edit".
I hope I have explained this properly!
Just noticed that if I place my mouse on any cell and hit delete (even on a blank cell) the hyperlink "friendly name" appears again.
There are several online tools available that can help you create comprehensively designed flowcharts and graphs within few mins. These could be flash animated charts that could go in to a ppt slide..
Read more here.. http://askwiki.blogspot.com/2011/09/best-of-online-graphs-and-charts.html
Hi again folks
Sorry to be a pain, I notice there hasn't been any activity on this for a while, but has anyone got any ideas on how to solve the hyperlink "Friendly Name" issue above (post 72)? I can make the "Friendly Name" appear successfully again (manually) by selecting a blank cell and then hitting "Delete" (I have also created some vba to do this with the selection change event), but when the mouse hovers over the "highlightSeries" UDF hyperlink above, it does not reset the "Friendly Name" automatically.
Any ideas?
Hey Alex - I'm trying to recreate the problem you're talking about, but I'm having trouble. Would you be able to put your file up for us to download?
Jordan, I am having the same issue where I've added the Else and it breaks.
Also, is there a way to make it activate on a Click (MouseUp or MouseDown) as opposed to a hover?
@Ryan - Did you copy and paste the code directly from this messageboard? If so, the quotation marks the message board uses ( “ ) are actually different from what vba uses ( " ). Take a look at your code and see if there are slanted quotation marks.
Second, you do not need the hyperlinks for click effect. Chandoo has a good post here on how to do it: http://chandoo.org/wp/2011/02/22/the-grammy-bump-chart-in-excel/
@Alex - I see the problem you are describing. My best guess is that the way the way Excel is programmed, anything that "points" (as in a memory pointer) to a cell will automatically change as well. However, what might work is if your Hyperlink formulas instead said HYPERLINK(INDIRECT(O1)). This will change whatever you hyperlink to, to the VALUE of the cell being referenced in O1. Specifically, if what's being referenced in O1 brings the user to a title of a column, you could always place a cell to the left O1 that says "Edit:" and the title of what they want to edit will show up. I Hope that makes sense. You can email me (jpo645 at gmail) if you need more explanation.
@Ryan - just to clarify. if there are slanted quotations in your code (because you copied it directly from chandoo), go ahead an retype those quotations.
Thanks Jordan,
I think I follow what you say, however, I have tried using an "INDIRECT" reference within the hyperlink but this still changes the "Friendly name" to the formulae value rather than the "friendly" value.
Essentially what I have is one Worksheet called "" which is where (as you would have guessed from the name) all my data is stored, I then have an "Indicator" Worksheet which is where the menu resides (Chandoo's excellent highlightSeries/valSelOption code).
I have set up a search function in my "Indicator" Worksheet which allows the user to search for a particular "Performance Indicator" - the results appearing in a range of cells below the "Search" cell with a summary for that particular Indicator (the user able to switch between different Quarters (Q1/Q2/Q3/Q4) using Chandoo's Function).
Next to the "Search" cell (in cell "G2") I have an "Edit" button which contains my hyperlink as below;-
=HYPERLINK(LEFT(RIGHT(CELL("filename"),LEN(CELL("Filename"))-SEARCH("[",CELL("Filename"))+1),SEARCH("]",RIGHT(CELL("filename"),LEN(CELL("Filename"))-SEARCH("[",CELL("Filename"))))+1)&"''!"&O1,"Edit")
Cell "O1" referenced in the Hyperlink formulae above contains the hyperlink to my "" tab which takes the user to the correct location for the currently selected Indicator and allows them to edit the data.
Everything works like it should, with hyperlinks going to the correct location for each specific indicator etc.
However, the problem is that when I hover the mouse of each of the Quarters, again everything works as it should and the relevant data is displayed accordingly, but the "Edit" button changes from it's "friendly name" to the value hyperlink value of the formulae above, which in my case is "[QDC Summary 11-12.xlsb]''!A$1", rather than the "friendly name" of "Edit"
I hope I have explained this properly, but please let me know if I have confused you and any advice or help would be greatly appreciated.
Thanks, Alex
Sorry, my post above seems to have been truncated somehow, my first worksheet is called Database2 (between a lower than symbol), but I don't think this website likes using those symbols in a post?
Also, my formulae posted above is cut off so i will try and split it;-
`=HYPERLINK(LEFT(RIGHT(CELL("filename"),LEN(CELL("Filename"))
-SEARCH("[",CELL("Filename"))+1)
,SEARCH("]",RIGHT(CELL("filename"),LEN(CELL("Filename"))
-SEARCH("[",CELL("Filename"))))+1)&"''!"&O1)`
Sorry for the double post
Hi,
thanks for same. Can u share Example file on this....
Please....
@Alex. Here is the solution that I've come up with. Instead of using a cell with a hyperlink formula to link to the original data, use a shape and a macro. First, insert a rectangle or a textbox - I inserted a shape called Rectangle1 and typed "Edit" into its textbox. Then I right clicked the shape, clicked "Assign Macro," and assigned it to the sub Rectangle1_Click. In the code I wrote something like this:
-
Public Sub Rectangle1_Click()
Range(Sheet1.Range("O1")).Activate
End Sub
-
So here's what's going on. In cell O1 you said that you were storing the address of the column whose data was currently being displayed on the chart. Here you'll have to change O1 to simply show the address in a way the VBA will understand (taking out the brackets and stuff) so for each column header it only shows the worksheet tab and address (with the filename removed). When you click on it your "Edit" rectangle, Excel will read the location stored in O1 and then "Activate" it. When it activates, it moves the Excel window so that the location is on screen. I hope that helps. Feel free to contact me if you need an example file. In the mean time, I look forward to Chandoo's post on how to better format my comments 😛
Thanks for your help once again Jordan, unfortunately I have tried this method to no avail.
All the macro does (for me at least) is move the cursor to cell "O1" but it does not activate the hyperlink in "O1" it just selects the cell. My current hyperlink value in cell "O1" is returning `#'Database2'!$C$171` (this will change dynamically according to the currently displayed data). The formulae I have in cell "O1" is;-
`=HYPERLINK(RIGHT(VLOOKUP(P2,$E$100:$F$200,2,0),
LEN(VLOOKUP(P2,$E$100:$F$200,2,0))-2))`
I hope this helps explain things a little easier.
Make sure you're reading the information from within cell O1. That is - Range(Range("O1")).activate - *NOT* Range("O1").Activate. Would you be able to send me your worksheet?
Hey, Alex. I think you bring up an intriguing functionality that should be discussed more at length. I'll do in article about your question for my blog (either tonight or tomorrow) and let you know when it's posted. I'll include examples as well.
Thanks Jordan,
Actually, I see where it's going wrong! If my link in "O1" is to a location on the current worksheet (where the Hyperlink and "Edit" button reside) then the Rectangle "Edit" link works fine. However, if my link in "O1" is to a location on another worksheet, that is when the hyperlink fails.
I have tried various combinations for the hyperlink value such as ;-
`#'Database2'!$C$171
'Database2'!$C$171
[QDC Summary.xlsb]'Database2'!$C$171
#[QDC Summary.xlsb]'Database2'!$C$171
But none of them work "Activate method of Range class failed".
As stated above, if my link above is pointing to a location on the current worksheet $C$171, then the Rectangle link works perfectly.
Thanks again, sorry for being a pain!
Any more ideas on the Hyperlink "Friendly name" issue above? Jordan, you still there?
I notice there hasn't been any activity for a few days now so either everyone has gotten bored of me asking the same question over and over (understandably) or the whole Universe has imploded and everyone has been sucked into a supermassive black hole. However, since I am typing this, I don't think the latter has happened, yet, unless I am now in some kind of weird alternate Transcendental Universe.
I digress - I am still experiencing the same problem as above, where, if the hyperlink is to a location on the current worksheet, the Hyperlink "rectangle" work-around that Jordan gave me works a treat, but if the hyperlink is to a location on another worksheet, I get the error `“Activate method of Range class failed”`
Any ideas?
@Alex. I finally got around to posting the article that answers this question. You can click the link on my name to see it. But, if you want to save time, you'll really need to keep track of two things for the edit button: (1) the tab that holds the data; and (2), the address of the data. Once you are keeping track of those things all you need to do is activate both. First, activate the worksheet only. Next, activate the range. You must go in that order.
Hope that helps!
Thanks Jordan
I have successfully used your code posted on your blog;-
`Dim wsCurrent As Excel.Worksheet
Set wsCurrent = Worksheets("Database" & Range("valSelOption"))
wsCurrent.Activate
wsCurrent.Range("B2").Activate`
However, I have adpated it slightly as some of the code was not relevant to my particular situation (I reference Database2 solely, so had no need to pull valSelOption into the link.
My code is thus;-
`Dim wsCurrent As Excel.Worksheet
Set wsCurrent = Worksheets("")
wsCurrent.Activate
wsCurrent.Range(Sheet2.Range("O1")).Activate
ActiveWindow.ScrollRow = ActiveCell.Row`
The only problem is there are still other Hyperlink formulaes on my worksheet/workbook that suffer from the "Friendly Name" phenomena mentioned above and although i have created a workaround for this (using the Workssheet_SelectionChange) event, so that as soon as the link is fired using Jordan's hyperlink code above, it selects an empty cell and deletes it so that the "friendly name" appears as normal.
Now, although this is perfectly functional and works as it should, it just "feels" clunky. Does anyone know of a better way of achieving this? I know I could remove all hyperlinks in my workbook and use Jordan's code above (using a link via a rectangle/shape), but I would have to replicate this down the entire workbook, creating individual shapes/code for each shape for the number of records I have (hundreds) and although this would work fine, it would take an age and the thought of doing this is just too much to bear!
Also, I've noticed that when I use this code via the code attached to the shape/rectangle, the `ActiveWindow.ScrollRow = ActiveCell.Row` code does not fire. Can anyone help me with this also?
Cheers guys and thanks once again jordan.
Alex
Jordan
Please ignore my post directly above regarding the `ActiveWindow.ScrollRow = ActiveCell.Row`, I had forgotten that I had written another bit of code so that when the `` tab was activated, it locked the currently selected section of worksheet in place to prevent scrolling to areas I did not want people to see. Unfortunately I only had the code to "unlock" the sheet firing when the current sheet was deactivated.
Now, I have injected code into your original code above to temporarily disable the "lock" code, run the hyperlink code, then re-enable the "lock";-
'Sub Rectangle2_Click()
Dim wsCurrent As Excel.Worksheet
Set wsCurrent = Worksheets("")
wsCurrent.Activate
'This is the code to "unlock" the scroll area
ActiveSheet.ScrollArea = ""
wsCurrent.Range(Sheet2.Range("O1")).Activate
ActiveWindow.ScrollRow = ActiveCell.Row
'This is the code to "lock" the scroll area to the current view
ActiveSheet.ScrollArea = ActiveCell.Resize(26, 15).Address
End Sub`
The hyperlink code now works like a well-oiled machine. I still have the "friendly name" problem on other hyperlinks, but this can be worked around.
Thanks very much for your continued help and the code on your blog `http://optionexplicitvba.blogspot.com/`, it is much appreciated!
Alex
Hi Chandoo & Friends,
Thanks for awesome tips in excel. This one rollover hyperlink technique is good one.
I am having a doubt, In this code, is it possible to show all 4 chart bars,( now showing individually) if scroll a particular cll marked as ALL.
Thanks & Regards,
PM
Just wondering if its possible to have the graph do multiple columns at one time? So using the example above I'd like to plot both Sales & Expenses on one graph, profits on another and customers on the third. So there would be 3 tabs in total.
I think its a great tool - but limited if you can only display one series on each graph.
Thanks, Sam
Is this possible to add this Chart in PPT?
If yes, please let me know how it is?
Thank you - your work is elegant!
I'm having some difficulty gettng it to work when setting up from scratch and have a technical question as well.
As far as the difficulty I can't get the rollover effect to work, where do I paste that code for the USD?
Second, if I wanted to bread it down further and differentiate between say 4 sales people, How do I sort through them and then use the graphs as you have set up? I thought that the filter function would work for this, but not so much.
Update, I got the sorting to work so you can ignore my second question above, and I fixed a number of the other bugs, but still have two issues.
I can't get the UDF code to work in my workbook (but if I import my worksheet into your workbook it works fine), and I can't get the chart title to change with the rollover.
Unrelated I was wondering if you know what type of function I would need to plug enterred data on one sheet into another sheet. So say with your file you got new data in for the next year and wanted to have someone add it without worrying about the integrety of the existing data. Is there a way to add it on another sheet and have it imported to the bottom of your columns where the current table ends. (I'm also assuming you'd have to add several rolls to the match formulas to have it auto read the additional info)
@Jeff
You will have to save the file as a *.xlsm or *.xlsb file type, not a *.xlsx file type
did that already, its xlsm
Perhaps I'm putting it in the wrong place.....
I'm hitting alt+f11, code and pasting it in there. Is that correct?
The error it gives me is that I cannot open the file.
Also if I have two different tables it is pulling from, is there a way to use if/then or some other command to switch the formula depending on which table is used?
Jeff, I had the same issue. It's the " in the code in the VBA module, type them yourself and it ought to work.
Question: Is this possible without using macros at all? Especially if I don't really care about the mouse roll over part - assuming I am fine with users clicking instead of simply rolling over ?
I have been building dashboards without macros, if I can help it. Too much hassle getting everyone at work "enabling" macros since my dashboards are passed around quite a bit. Possible?
Hi Hui
Is there anyway to have the previous and new numbers side by side in the same graph?
if you're asking what I think you are, you could add a second data series to the graphs data selection.
I am having trouble adding a second series.
Can anyone upload a version with two series in the upper graph for me please.
This is great, I am not that great in Excel do you think someone can help me a templet format for this.
This is great, is it posible to do this chart with 2 series? Please HELP
I successfully edited the data and was able to add in a new column of data to the dashboard, but the chart area is not working exactly.
Really just the tan color section of the chart. Now that I added in a 5th data set, when I roll over that selection the chart shows the data but there is a white column at the end not all tan like the others.
Just move the chart and it's simply cell formatting underneath.
Thank you!
It took me awhile to figure out how to alter it to be able to show YOY collumns so I could compare two years (ended up just doing IF formulas rather than the Index formula)...once I did however it was very slick and will be great to use for reporting.
Thanks Jon....
now Would u again please help me how we can put Graph name in above chart. As I forgot to add name. As I tried a lot,but still not able to update this automated name.....
Please help in this issue.....
This works fine.
My next question is what about if you wanted to have multiple data sets on one sheet.
For instance multiple charts on one page.
Hello,
We can use this same login for Multiple iteams also. but, for same we have to give range as chart title.
Whats the protocol to have multiple charts using different data values.
It's awesome. But, If I protect the worksheet, It will not work. How can we overcome this problem
Many thanks
I got a solution, just unlock the necessary cells (series data, valseloption). Anw, many thanks for this masterpiece
I showed this to my boss and he loves it!
He was wondering if I could graph 2011 data into it. For instance have both 2012 and 2011 revenues displayed side by side for each month displayed?
Is this possible?
@Mike & U R
I have made up a sample with 2 series
Refer: https://www.dropbox.com/s/u2ybryrjhyaznxr/rollover%20hyperlink%20%282%20series%29%20demo.xlsm
I will let you examine it first and come back with questions
It works fine in Excel 2010 and 2013.
Do you need a 2003 version?
Hi Chandoo and Hui:
Your site is phenomenal!!!! I have been knocking socks off left right and center since I discovered your blog/site.
Here's a question...I am creating a dynamic dashboard using hyperlinks and 2 series. I am running into trouble because I want to transpose the position of the dashboard summaries so that they go down my worksheet in rows or a single column, rather than across the top (I hope that makes sense), because I have several. BUT...I can't get this to work in terms of the conditional formatting and highlighting. Can you please help by way of sending me an example of what I am doing wrong?
I would really appreciate your help. Thank you!
@Linwe
Converting to a vertical format is quite simple
Simply select each cell and drag it by dragging its boundary to its new location. All the formulas adjust automagically
Or even simpler is to see the sample file in a Vertical Format below: https://www.dropbox.com/s/7bwgw9riw345lvx/Rollover%20hyperlink%20%282%20series%29%20Vertical%20Demo.xlsm
Thanks SO much. I was over complicating things and couldn't see the forest through the trees.
You are the BEST!
Hi Hui
This link does not work anymore, would be very interested in seeing your example.
@Ajay
The link works fine?
Can you retry or have someone else check it
[...] Adding interactivity using Hyperlinks [...]
Hui,
It worked perfect! Thank you so much!
In my original I was trying to assign the series in the macro to series1 and series2.
This one blew my boss away!
Msquared
[...] Interactive dashboards using Excel Hyperlinks – tutorial & explanation [...]
Thanks a ton Chandoo!
hi
I have a very basic doubt . I tried to do exactly how it is shown in the video link
http://www.youtube.com/watch?v=T7eDQqPZOm8&feature=channel_video_title
I am unable to replicate the changes done by hovering of a mouse . This is essentially a problem i face while doing any macro code .
Can some one please go thorugh the attached file and be so kind to guide me on how to do this ?
Any help will me much appreciated .
Link to the file below
https://docs.google.com/open?id=0B_ROIRDr5-DSS2w3QTRjektEbHc
[...] basic ingredients of this chart are Interactive Hyperlinks, Conditional formatting, Form Controls, INDEX + MATCH formulas, Picture links and [...]
i love this code and in fact I plan to use it displaying energy tracking. I would like to use multiple charts on one page. I have created the multiple charts and it works however they work together in sync. I would like to use them as separate charts. Can you help?
Steve - try making an additional user defined function for each individual chart. For example, if you have two charts, you might add another rollover function in your module called highlightSeries2 which uses range valSelOption2. Then reference highlightSeries2 in a separate Hyperlink formula. Now you'll have two different rollover mechanisms going at once.
Thank you so much, this worked like a charm!
Thanks a lot, it's been very useful to hear from you Mr. Chandoo.
Is there any way to draw 2 lines in each graph? I need a year-on-year comparation.
Can this be linked to ppt?
Regards,
Garcia
Sorry, I read replies in this post and did a year-on-year analysis.
What about this type of interactive graph in ppt? Thanks.
[...] What did you try? Maybe you can adapt the example here: Interactive Dashboard in Excel using Hyperlinks | Chandoo.org - Learn Microsoft Excel Online [...]
[...] Link: http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ [...]
Please Help, can you make this interactive dashboard for portfolio candlestick chart so I can see different chart of company like this one. Please help Chandoo.
Hi All,
I have got this Rollover working well now and have it set up on multiple tabs. Its fantastic.
Like most dashboards though the purpose is to asses quickly the performance of X against Y. What I am really struggling with (and I am not entirely sure this is possible) is to have 2 inputs into the graph (Eg 2 lines in a line graph).
1 for Actual results
1 for forecasted results
Currently I can only get it to focus on one line column of numbers. If I could use this dash board for this type of comparison on 1 rollover but it would be the icing in the cake if it is possible. I have tried a few things but it goes wrong very quickly.
I am not sure I explained it well, do you understand what I am getting at ?
thx
Neil D
man, you're GREAT !!!
that's exactly what I have looked for a long long time. You saved my day
laloune
Thank you for this code! I had a major issue with screen flicker. When hovering over the down arrows the chart begins to flicker wildly. This would occur only when clicking between tabs or when using hyperlinks between worksheets. I spent a day or two just on that but could not resolve the issue. By accident today I found the code below resolves this issue.
Private Sub Worksheet_Activate()
Application.SendKeys ("{ESC}")
End Sub
[…] Mouse is over a Cell by MeHow to: highlighting cells using the rollover technique in Excel by MeInteractive Dashboard in Excel using Hyperlinks by […]
Thank you for the tutorial! This is awesome.
Is it possible to use this technique to show/trigger a message box or a text box on a chart? For instance, I have a pie chart with plot base on 4 regions (east, midwest, west, south). The pie chart only shows the total of each region, is there a way when a user hover over/mouse over the data label, and it will recell/show a box indicate what the total $ is coming from. So east total is $150 = it comprises of carolinas (30), florida (40), boston (80).
Having it show up directly over the pie could be done but its very tricky because of how much the slices shift. If you don't mind a slight change then..
To do this, first create what you would like your popup to look like somewhere else. This may require you to use some formulas that dynamically show/calculate cities and totals based on what region is being set in some named range.
Second, copy this popup area and paste it anywhere on the chart page as a linked image.
Finally, set your legend up externally from the chart (meaning don't use the legend feature of the chart, make 4 cells with colored shapes as a custom legend), and use the hyperlink function to work in the 4 cells being used as a legend for the 4 regions. It should feed the named range that the popup is based off of above. The code should adjust the location and visibility of the linked image so that it shows up and arrives at a location based on your currently selected region in the legend.
Can this work with more than 4 series of data? I have 10 but there is not that much data within them. Any ideas?
Thanks,
Steve
Awesome Chandoo!
Thank you very much for sharing this with us.
This is fantastic, I've used this within my dashboard and it offered me great flexibility. I have a question about data labels in the graph. Numerically I have different types of data which either need a £ sign or not. For example:
AVE which is £x,xxx,xxx
Volume: x,xxx
Cost per transaction: £x.xx
Within the match function, the numbers are formatted correctly but when I create a graph - it doesn't show properly. The numbers are all the same type (without the pound sign and the CPT rounds the number up). Is there any way of having unique number formatting within excel graph data labels?
Thanks
Lucy
[…] (like Rollover Beethoven…? get it?)Update 17 July 2011: This blog entry was featured in Chandoo’s latest post. So if you’re coming here from his site — welcome! original […]
[…] basis of the article surrounds a question in the comments section of an article on Chandoo.org (comment #75) written about the hyperlink() method I discovered and wrote about here. In […]
[…] Hyperlink: Read also the information and explanation on the website of Chandoo.orghttp://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ […]
[…] of last year when I posted an exciting discovery: the Rollover technique. This technique was written about by the wonderful Purna of Chandoo.org. At the time, I was super excited about all of the possibilities of this technique. Now, just about […]
This is very simple yet innovative. Thank you for sharing
hi guys
I have managed to do this sucessfully in a different spreadsheet, however I am currently trying to add this to a different dashboard and having problems with my hyperlink formula
Everytime I enter the formula =IFERROR(HYPERLINK(highlightSeries(B39)),"6")
it automatically defaults the "S" in series to display
=IFERROR(HYPERLINK(highlightseries(B39)),"6")
therefore the entire chart does not work (I am assuming this is the issue)
does anyone have any ideas why the spreadsheet would do this? i have done this numerous times in other workbooks and not come across this problem, any advice would be appreciated! 🙂
Thanks for the post
I made this successfully in excel 2010 but 2013 doesn't seem to like it? Anyone else found this?
This doesn't work in excel 2013! Anyone else finding this/solution?
[…] http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ […]
OK, I love this website, thank you for all the great tips, however, I cannot get this to work and it is driving me crazy, I cannot figure out why. If anyone is still monitoring this project let me know and I can send the file over that I created for you to point out my silly mistake. Thank you in advance, because I am pulling my hair out!
@Nick
Yes, All posts are monitored nearly all the time
You can email me or post the file on a sharing site like dropbox
Hui...
Hello all, thanks in advance for the question I am posting...
After reading through each of the comments, I haven't seen a solution for the cells that have the "6" arrow w/in it, in which the text is to be altered to "Wingdings". What is currently displayed is the formula in Wingding text as opposed to just the arrow. Any thoughts on my error? I am using version 2003. The Interactive Dashboard is working & this is the only thing I need to resolve. The code in the cell is:"=_xlfn.IFERROR(HYPERLINK(highlightSeries(B2)),"234")" Many thanks, M.C.
It is working now; apparently a bug that I evenutally bypassed. Took the code out saved, put the code back in,...viola!
For those of you that are struggling w/ this, backtrack & make sure that you are doing EVERYTHING to a "T" as the instructions demonstrate & this works wonderfully. It took me a couple bits of backtracking to see what was missed and/or misunderstood. Good luck, M.C.
[…] http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ […]
[…] googletag.cmd.push(function() { googletag.display('div-gpt-ad-1322710633957-0'); }); This is what I use. Hope it helps. Interactive Dashboard in Excel using Hyperlinks | Chandoo.org - Learn Microsoft Excel Online […]
Hello,
How easy would it be to have the chart show two columns worth of data? For example, the sales tab of this dashboard shows actual sales by month from January - December 2010. Would it be possible to show budgeted sales next to actual sales for those periods?
Thanks,
Jim
Sorry, found my answer in the above comments.
Thanks,
Jim
@Chandoo: I've tried to do the dashboard as you given first..But, the user-defined function valSelOption is not working..Please suggest..
Many Thanx,
MK
Thank you for this aweson report, i use it to create a YTD Report
This is supercool! I have successfully replicated it in my dashboard. Now, I am trying to see if I could use these hyperlinks to switch sheets. I.e., by calling a macro that switches the sheet based on the value of the Named Range within a Worksheet_Change event. However, this is not working. The end result, if you help me make this work, will be a dynamic dashboard where sheets get activated with just a mouse over. Thanks for looking into it
Thanks Chandoo. This is really an amazing post.
And thanks Jordan, Your comment with 2 chart thing helped a lot
Anas
Chandoo this is an amazing technique but I ran into a little trouble.
I have a hyperlink/rollover in a workbook. The function is supposed to change a field in a pivot table. The function will ClearAllFilters on the pivot table and even clear the filters on a slicer. However I cannot get excel to put a new value in the pivot field to filter the pivot table. The code will run without error but will not update the pivot table. I can use the same code in a normal function and it works so I'm sure it's not the code. Can you give me advice?
This is amazing! It has served as an inspiration for my first dashboard at work.
I have a small problem, however. My dashboard is saved on an OpenText Content Server, where I have added a couple of List Boxes to help highlight another data series on a line graph.
The List Boxes work beautifully when the workbook is editable. However, for those users that are accessing the workbook in read-only mode (the only option they have), Excel asks whether the workbook should be reopened when an option is selected in the List Box.
I have tested this by saving the workbook locally instead of on the server and the List Boxes work correctly then. Therefore the problem seems to be with the server, but can anyone give me any pointers as to what could be causing this message to appear?
Hi Chandoo,
This is amazing and my boss was impressed when I used this on my first dashboard. But I was wondering whether it would work on touchscreen devices i.e. you can rollover using your finger instead of your mouse.
Thanks for the posting this technique. It is very cool.
I ran into a problem though and wonder if you have solutions if I still want to use hyperlink : in your example the data ranges are close. But what if I have one set of data in thousands while the other one needs to show %? I don't know how to change axises if it's possible to change in this case.
Thanks again for all the amazing tips!
April
I am having trouble creating the valselOption, it will not reflect any column values to the left of the fifth column. Is valSelOption a formula inside of name manager or an index formula or match formula?
@Jon
ValSelOption is a Named Formula
You need to set it up in the Name Manager
Hi
Can I publish the same to a PDF file with the same effect?
[…] 10 sources for your website map to the top 10 landing pages. The workbook uses a mouse rollover, or interactive hyperlink technique so you simply move the cursor over the text in the left (source/medium) or right (landing […]
Ok, I have a fromula =HYPERLINK("#A100") that activates the cell A100. How to combine it with =HYPERLINK(highlightSeries(A100)) to get same result, i.e. just hover over the link and then activate cell A100?
@Tom
Have a look at the techniques discussed here:
http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/
You should be able to adapt them to your needs
Ty for replay. I read comments. I tried to adopt but I can't achieve desired effect.
@Tom
Can you please ask the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please supply a sample file to make the solution more relevant
Great it is... But its not working on locked sheet (as Dashboards) even leaving those hyperlinked cells unlocked. please suggest..
Hi Anhsu, leave your linked Cell unlocked , In this case is "AC17" above of the "ValSelOption". And it will work
[…] you wish to trigger an action on hovering over a cell there is a technique described here by […]
Hi - awesome technique
A very quick question- With chandoos example where he shows the number 6 down arrow - is it possible to wrap the formula so it shows the right arrow number 4 when i hover over the cell and i move away from the cell and not hovering over that cell then show the number 6 which is diwn arrow on webdings?
Dear Sir,
please tell me i m trying f2 hyperlink formula but showing in special character how to be possible in excel.
regards
vijay
For a new example, with some unexpected features, see: https://dhexcel1.wordpress.com/2017/09/15/highlighting-words-in-an-excel-list-using-the-hyperlink-rollover-method/
Hello this is great, is it possible to do same thing in Powerpoint?
What is the conditional formatting done over here? I tired many different ways, but was not able to find the conditional formatting. Thanks
My valseloption column is only displaying my first 12 items correctly. Everything after this displays the value that was in the 12th row. Please help.
Hi, how can we use this for a two column data, for eg last year and this year
Hi Chandoo
I know this is 10 years after you have posted this so really hoping this gets through somehow..
Developing a massive headache. Have got this working well in my scripts
However if I actually want to click on the cell and create a link via e.g.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
then it will fire some of the code but things like activating a tab or activating another cell don't work or fire an error
Is there a way to have the cake and eat it too?
e.g. have the hover link style but also have the links work ?
@Rob
Can you ask the question in the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to assist in getting a quick response
Not sure if this is possible. May be I am wrong.
One other option is use hyperlinks for hover and a small textbox / shape next to it for another click action.