Excel chart problem - need help

Excel chart problem - need help

Post by Paul Bir » Wed, 02 Jul 2003 05:20:11



Help, I know that this may be simple but I am a novice at using Excel and am
having trouble with charts.
 I want a chart to automatically update some values based upon which section
I select from a drop down list of some sorts.

I have three sections of people, Section A has 21 staff, section B has 14
Staff and section C has 21 staff, set out as follows:

Section A has 21 staff, each one anonymous and labelled i.e A1, A2, A3,
A4.........A21
Section B has 14 staff, B1, B2.....B14
Section C has 21 staff, C1......C21

Like below:

Section       Staff number     hours worked
A                 A1                        5
                    A2                        4
                    A3                        7

B                 B1                        1
                    B2                        8
                    B3                        10

C                 C1                        6
                    C2                        4

I want to create a chart where I can select the section  letter(A, B or C)
from a drop down menu of some sort and the chart will automatically update
with the staff numbers along the X Axis and the hours worked will be
displayed in the values section. Can anyone help me with how to do this?

Thanks

Paul

 
 
 

Excel chart problem - need help

Post by Rommert J. Casimi » Thu, 10 Jul 2003 17:09:00



Quote:> Help, I know that this may be simple but I am a novice at using Excel and
am
> having trouble with charts.
> I want to create a chart where I can select the section  letter(A, B or C)
> from a drop down menu of some sort and the chart will automatically update
> with the staff numbers along the X Axis and the hours worked will be
> displayed in the values section. Can anyone help me with how to do this?

As a novice you should start with recording a macro that builds an example
of the graph. When you are familiar with recording and executing macro's you
can change the visual basic program. You can call the macro automatically
when you select an item from the list. Take care that both the number of
departments and the number of persons in a department are variable.
Proceed slowly because the expresssions in the VBA program can become quite
complicated. For example, I put the lists of hours worked in B:J, the names
of the departments in K and the selected department in L1. My macro is:

Sub Macro1()
    Dim S As String
    S = Chr(Asc("A") + Range("L1")) + "2:" + Chr(Asc("A") + _
     Range("L1")) + Trim(Str(Cells(1, Range("L1") + 1) + 1))
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(S)
End Sub

Graphs created with macros can give all sorts of trouble, so be careful

 
 
 

Excel chart problem - need help

Post by Lynn » Fri, 18 Jul 2003 17:41:54




> Help, I know that this may be simple but I am a novice at using Excel and
> am having trouble with charts.
> I want to create a chart where I can select the section  letter(A, B or C)
> from a drop down menu of some sort and the chart will automatically update
> with the staff numbers along the X Axis and the hours worked will be
> displayed in the values section. Can anyone help me with how to do this?

How about filtering your data rather than the chart? All you need do is
create a chart with all your sections/departments in, then select a cell in
your data, go to "Data", "Filter", "Autofilter", and select whichever
section/department you want to see. No tricky macro code - all you have to
do is make sure there are no mistakes in your section/department labels, to
ensure you get all the right data.

Lynne Cartwright

 
 
 

1. Excel pie chart problem-need expert help

I have an Excel VBA macro that creates pie charts along with some other
monthly reports.  The problem is that occasionally (2 or 3 times out of
100), the text on the pieces of the pie overlap.  Since these reports
are run by the hundreds, it is not feasible for someone to sit and check
each one.  I have tried resorting the pieces of the pie to get the
optimal sort order, but it won't work 100% of the time. Has anyone else
run into this problem or found a workaround other than manually checking
and rearranging pieces of the pie?  Don't say "use a bar chart" or
something of the sort because that is not an option with this particular
customer.  I can get the top and left properties of the text, but that
still doesn't tell me if the pieces overlap really.  Any help would be
appreciated.  ct

2. Application Licensing

3. Excel Charting: line/column combo chart problems

4. Crossreferencing bibliography

5. Please help! Excel AREA CHARTS and LINE CHARTS

6. PC/104 and serial port

7. Excel pie chart probs - need help

8. Canadian MO's and 8mm drives

9. Microsoft Excel - need help with charts

10. Help needed: GANT chart in Excel 5.0 or 4.0

11. Help needed with excel charts and word

12. *** HELP with WYSIWYG problem on Excel charts ***

13. Excel: Drawing into chart using chart coordinates