Results 1 to 5 of 5
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Subtotals & Data (Excel 2002 SP2)

    I'm developing an Excel "report" that utilises a large data set from the mainframe, the process is very manual. I manipulate and sort the data, then subtotal by category. So I can use these category subtotals to poulate the report I have to copy visible cells, find and replace the word "total" with nothing, paste, special values the subtotal values to remove the formula, trim the category code and paste special back the category code before it works with vlookup
    Is there a better way to accomplish this or do I have to <img src=/S/grin.gif border=0 alt=grin width=15 height=15> and bear this? I have attached a small example
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Subtotals & Data (Excel 2002 SP2)

    Try using array formulae as in the attached version of yopur workbook.

    If H2 contains 15151, then th efollowing formula will return 3,999.60

    =SUM(IF($C$2:$C$604=H2,$D$2:$D$604,0))

    That formula is entered with Ctrl-Shift-Enter so that it is enclosed in brace brackets.

    Andrew C

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

    Re: Subtotals & Data (Excel 2002 SP2)

    I think a pivot table would be easier:
    - Create a pivot table based on the raw data.
    - Use the GETPIVOTDATA function to retrieve data from the pivot table.
    See attached zipped version.

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Subtotals & Data (Excel 2002 SP2)

    Thank you Andrew, it does simplify matters a lot.
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Subtotals & Data (Excel 2002 SP2)

    That is a method I had not even considered, that's great, thank you Hans. Certainly makes life easier. <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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