Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Suggestions (2003)

    I've been given a task by our MD, and don't know the best way to approach it. He wants a chart to show what percentag of the working week, is spent on different tasks by our maintenace department. To make it easier for them to enter how long they were spending on each job, I created a form in Access, and tried creating a chart. Trouble is, the calculating of the charts in Access causes the results to be over 100%. Excel would be better, but I need them to be able to input the data daily, and then when the chart is required, input a start date, and an end date. These guys don't have much experience with spreadsheets, so I want to make it as easy as possible just to enter data, and print the chart. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestions (2003)

    If this is related to <post:=593,332>post 593,332</post:>, you'll probably have the same problem with rounded percentages in Excel as in Access.

    In Excel, you could unlock the cells where users need to be able to enter data, then protect the worksheet. That way, the users can't accidentally overwrite formulas etc., and you can specify that users can only select unlocked cells.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions (2003)

    It seems like that chart will be drawn from a list of some kind since you are taking about a start date and en date. Another way to input data, esp. into a list is to use the data form. This will give the input a look and feel such as an Access form. You can still lock the cells as in Hans's suggestion if you do not want people tampering with calcs, etc.

    Once the data is inputted into the list by the data form (or typed), you can base charts on the filtered date range using Autofilter.
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Suggestions (2003)

    If I put an Autosum further down, and use autofilter, can I get it to total up, just the rows I have filter. You see, if I have 5 rows (each with a seperate date) I just want the total. To explain, I have 11 categories of jobs, each day for a week, they will input the hours spent on each. That will make 5 Rows 11 Columns. The following week, they will had another 5 rows, etc

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions (2003)

    If you use the Subtotal function, you can filter out any criteria using the Autofilter arrows and the subtotal calc. with display the appropriate total. See the example file I attached. Filter the Case Type column to notice how the subtotal changes.
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Suggestions (2003)

    Thanks Rudi, that will be easier than having to create a database. I've learnt something about Excel I didn't know. Thanks again.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Suggestions (2003)

    Just one thing Rudi. How come the outline sysmbols don't appear on your example, yet they do on my worksheet, when you have it ticked on the options?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestions (2003)

    Rudi hasn't applied Data | SubTotals, he has only entered a SUBTOTAL formula in cell F22. The SUBTOTAL function is available for use independent of the outline created by Data | Subtotals. The interesting feature of the SUBTOTALS function is that it ignores rows hidden by the filter.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Suggestions (2003)

    Thanks Hans, that's excellent.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions (2003)

    If you do want to use subtotals, you can also hide the outline symbols by using the key combination : CTRL+8 (the 8 above the alpha keypad). This toggles the symbols on and off.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •