• 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.

Graph to show improvement ..

J

jei eusebio

Member
Hi Ninjas,

I was asked to make a graph that shows month on month data for multiple supervisors. I made a simple bar graph to show how each one is doing per month but I think it looks too busy. Is there a way to do this like a line graph? Where the names would be on the X axis and the scores would go through Y? or anyway that the data/graph would look presentable and not too busy .. I attached the file for reference. Thanks in advance guys! :D
 

Attachments

  • Sched Adherence.xlsx
    40.8 KB · Views: 3
NARAYANK991 said:
Hi ,

Something like this ?

Move the cursor in the Names column of the Pivot Table.

Narayan
Click to expand...


Hi NARAYANK991,

This looks nice and more organized than what I had hehe .. but when I drag the range for the name and scores, it gives all sorts of colors for the lines but only highlights the first 5 names in red when selected. Is it possible to do the color and the red highlight for the others? Thank you sir!
 
Hi ,

You are right ; I did it only for the first 5 names. I wanted your confirmation before I spent time doing the others. Will upload the revised file soon.

Narayan
 
Hi ,

A few changes are required to make it work on the first sheet.

1. There are 2 named ranges which have been defined Names and Months. These are referring to worksheet ranges on the tab named Enrollment ; you will need to change these to refer to worksheet ranges on the tab named Member Services ; note that since this tab name has an embedded space character , you need to enclose the tab name within the single quote character '.

2. There are 28 names on the first tab compared to 22 on the second tab ; you will need to add the additional 6 series using the Name Manager.

3. The code itself is using a Worksheet_SelectionChange event procedure ; hence this code is in the sheet section pertaining to the second tab. You can copy the code and paste it in the sheet section for the first tab.

4. Within the code , the range is hard-coded as $K$2:$K$23 ; you will need to change this to $K$2:$K$29.

5. You need to remove the existing chart on the first tab , and replace it with a copy of the chart on the second tab ; if you do not wish to do this , then you can change the code by replacing the index number for the ChartObjects collection of 1 by 2 , in the following lines of code :

With Me.ChartObjects(1).Chart

For Each shp In Me.ChartObjects(1).Chart.Shapes

Narayan
 
So sir, if I create new named ranges for the 1st sheet then I have to create new series names as well? If I rename ng current named range for Names and Month to work for th first sheet, would it have any effect on the 2nd sheet? or would it be better to just create new named ranges for the 1st sheet?
 
Hi ,

The first point is whether you want to retain the chart on the Enrollment tab , or do you want that removed , and instead have a chart on the Member Services tab.

If it is the former , then you need to have separate named ranges for the 22 series on the Enrollment tab , and the 28 series on the Member Services tab.

If it is the latter , then you need to do the following :

1. Change the definitions of the named ranges Months and Names so that they point to the ranges on the Member Services tab , instead of their original references to ranges on the Enrollment tab.

2. Add the additional 6 series so that all 28 names will have their chart series.

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