• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight Axis Labels Based on Selection

T

Twee

New Member
Hi,


I'm working on a lot of charts that compare 40 hospital performance to each other. Of those 40 hospitals, there are about 10 regions that those hospitals can be grouped into. As I'll be presenting data to these regions separately, I'd like to highlight the relevant hospitals in each region; usually the graphs have 40 bars, 1 for each hospital. Is there a way to highlight specific hospitals in each region that I select without having to resort to manually changing the axis or the color of the bars for each corresponding hospital in a region? That would be incredibly mind numbing!!


Is there an easy way to do this? I've tried overlapping series and it seems to work on straighforward bar chart but many of charts are stacked column graphs so this method seems to lose some information, unless of course I'm doing it incorrectly! Please, please help.


Any solution would be worth be so, so helpful!! I'm using Excel 2007. Thanks!!
 
What about the techniques Chandoo has used at: http://chandoo.org/wp/2011/02/22/the-grammy-bump-chart-in-excel/
 
Thank you, Hui for reminding me of this because I can't seem to think how I'll apply until you pointed it out. I'll give it try but am wondering if there's another technique, just in case...Reason I'm wondering is because all most of my charts are single chart with 40 bars (1 for each hospital at the bottom)with hospital names on the X-axis so i'm not sure what I would click on.


Originally, I was just thinking of using a quick drop down menu to change the region and the corresponding hospitals of the region would be highlighted on the graphs. Also, the idea of a bar highlighting several bars (for each hospital) might make the graph even more busy than it already is. Maybe I'm missing how I can apply the grammy bump technique.


AFter further description, do you this technique would still work? Thanks so much, Hui, for your help.
 
You could change the code that Chandoo has used so that instead of highlighting a single Column, that it would toggle columns on/off allowing several to be highlighted


Or you could do something like I uploaded at: http://chandoo.org/wp/2011/02/25/excel-age-survey-results/

Scroll down to the 3 Black charts.

The download link is just below them.
 
Hi, Hui,


Thanks for your quick response. It looks like Chandoo's grammy bump chart may work the best for my purpose. I like your 3 black charts and I understand how to do that and the only reason I can't do that is because my charts are stacked columns so "highlighting" it with one color would lose some of what I wanted to communicate.


My problem is I don't know VBA to modify Chadoo's seemingly simple VBA code...I'm already enrolled in the VBA school but am way behind on lessons so i am stuck. Only on Week 2 Lessons and can't seem to modify the code to work for my purpose. For instance, I need to highlight 9 hospitals that are in the Sacramento, CA region on a graph that has 37 hospitals/columns in the X-axis. I envision one pull down menu for me to select which of the 10 regions I want to highlight and the the graph would highlight the appropriate columns/hospitals.


Can you please help me modify the Chandoo's code? I've been modifying it but i keep getting errors. I wish I could catch up on my VBA lessons fast enough to figure this out on my own.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not (Application.Intersect(ActiveCell, Range("lstYears").Cells) Is Nothing) Then Call UpdateAfterAction

End Sub


Again, I'm sorry to keep going back to ask for help. Still have lots of learning to do in Excel.


Twee
 
You must log in or register to reply here.
Back
Top