+ Reply to Thread
Results 1 to 4 of 4

Automating data analysis

  1. #1
    Registered User
    Join Date
    06-24-2014
    Location
    Newcastle upon Tyne, UK
    MS-Off Ver
    2007
    Posts
    3

    Automating data analysis

    Hi,

    I'm new to the forum and new to some of the powerful things you can do in Excel. I recently set up my own home based business and have been trying out lots of new functions in Excel to improve how we record our accounts information and analyse it quickly. But I've hit on a problem I can't solve so I'm hoping someone here can help me out.

    I want to have an automatic summary analysis produced from my expenses data, based on which department I want to classify them to from a dropdown list. In the attached spreadsheet you'll see an example of what I mean. I'd like to program the spreadsheet so that when I enter a new expense and assign it to a department the summary boxes immediately fill in for me, so I don't have to do a manual calculation.

    I've tried using the DSUM formula but can seem to make it select all the expenses related to the sales department and add them up. So I might not be using the right formula.

    Any help at all would be greatly appreciated and will help me to improve my knowledge of Excel.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Automating data analysis

    Hi, and welcome to the forum.

    See attached.

    I've added a dynamic range name called 'Data' which will automatically adjust to cover all expense rows on the Expenses tab. I've also added a Pivot Table summary which will automatically refresh every time you select/activate the sheet.

    To make the data work it requires a single row of column labels hence I've slightly modified your data layout and also moved the total line above the data.

    The summary, being a pivot table means that you can freely move field names around and of course filter it as necessary.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-24-2014
    Location
    Newcastle upon Tyne, UK
    MS-Off Ver
    2007
    Posts
    3

    Re: Automating data analysis

    Richard, that's fabulous and thanks for responding so quickly. You've saved me a lot of time and pointed me in the right direction of learning how to use pivot tables. Thanks for sharing your knowledge with me. Best wishes Rob

  4. #4
    Registered User
    Join Date
    06-24-2014
    Location
    Newcastle upon Tyne, UK
    MS-Off Ver
    2007
    Posts
    3

    Re: Automating data analysis

    Hi again Richard,

    I've played with pivot tables in my spreadsheets and they're great. One last question please, how did you get yours to update automatically please? The only way I can get them to update as new data is added is to manually conduct a refresh. Is it something to do with you creating a dynamic data range? If so please point me in the right direction to set one up. Many thanks Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automating Data analysis across multiple worksheets in a folder.
    By briggsa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2013, 08:22 AM
  2. Replies: 2
    Last Post: 07-14-2013, 03:13 PM
  3. Automating a pivot table for data analysis
    By LeapingLizard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 06:19 PM
  4. [SOLVED] Analysis Toolkit does not work when automating EXCEL workbook
    By Dave Gruzewski in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-15-2005, 11:06 AM
  5. Problem using analysis toolpack when automating EXCEL
    By Dave Gruzewski in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1