Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro? Auto summarize data (2003)

    I'm attaching a file to show you what I need help with. We have data dumped to a spreadsheet every month or so from a database query. The data lists each dept's actual budget numbers in one long table. Our director wants the data summarized in separate tables for each department. At first, I thought of subtotals. But she wants the labor numbers first with the type of labor split (regular, Other and OT), then the total labor, then the Direct and Materials line items. I can't see how to break down subtotals the way she wants it to show.

    I thought about trying Vlookup, but either that won't work, or I'm just not clever enough to figure out how to use it for this example.

    How can I get the data to summarize in the required format? I am totally code illiterate. I can copy/paste if someone has the code for this somewhere.

    Would you mind, please, looking at the attached file and telling me how to set this up for her - keeping in mind that the data will be updated each month and there may be additional lines added to some of the departments each month.

    Thank you for any help you can provide.
    Attached Files Attached Files

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

    Re: Macro? Auto summarize data (2003)

    Since the data come from a database, I'd ask the person(s) responsible for the database to design a report in the database. That should be easier than trying to do it in Excel.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro? Auto summarize data (2003)

    I agree. But that's not an option. I already asked that question. Long winded reply that amounted to "No."

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

    Re: Macro? Auto summarize data (2003)

    How should the output be organized?

    One table with a dropdown to select the department?

    A series of tables below one another in one worksheet?

    A separate worksheet for each department?

    Or something else (if so, how)?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro? Auto summarize data (2003)

    The director said she wants a series of tables below one another in one worksheet.

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

    Re: Macro? Auto summarize data (2003)

    See the attached version. I've added a command button (from the Forms toolbar) that starts the macro.
    The macro code in the Visual Basic Editor has been commented extensively.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro? Auto summarize data (2003)

    An other formula way,

    Please select the department code number from the B3 dropdown list

    Regards
    Bosco
    Attached Files Attached Files

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

    Re: Macro? Auto summarize data (2003)

    Your formulas work well, but MelanieB stated "The director said she wants a series of tables below one another in one worksheet."

Posting Permissions

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