+ Reply to Thread
Results 1 to 5 of 5

Extrapolating spreadsheet data

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    Mobile AL
    MS-Off Ver
    Excel 2007
    Posts
    3

    Extrapolating spreadsheet data

    Ok, I'm not real sure how to put into words what i'd like my spreadsheets to do, so I'll simply show you a sample.

    I'd like to filter data from the first table and create another table similar to the smaller one. And, i'd like for it to automatically update as I add information to the larger sheet.

    What formulas would I need in cells F34, G34, H34 to pull and add the data together like i have it shown?

    Any help is great.

    Thanks,
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,294

    Re: Extrapolating spreadsheet data

    Welcome to the Forum!

    There are a couple of ways you could approach this. First, I would suggest moving your summary table out of the line of fire of your primary data, either in K16 or another worksheet.

    If you already know all the codes, and have a list of them in your summary table, you can used formulas with SUMIF. See example.

    If you do not know the codes in advance, then a pivot table is your best solution. I also included that. It would not update automatically (two clicks to refresh) but a macro could be installed to update it when the data changes.

    Edit: By the way, what you are doing is not extrapolation, it's summarizing. The thread title may have chased some people away
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-06-2011
    Location
    Mobile AL
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Extrapolating spreadsheet data

    Great Help, thank you 6stringer.

    Let me toss one more wrinkle at you...

    In the summary table how can I have it automatically add another code as a new code is added to the invoice?

    (see attached; blue and brown cells)
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,294

    Re: Extrapolating spreadsheet data

    Quote Originally Posted by djohnalyn View Post
    In the summary table how can I have it automatically add another code as a new code is added to the invoice?
    Quote Originally Posted by 6StringJazzer View Post
    If you do not know the codes in advance, then a pivot table is your best solution.
    To refresh, right-click on any data element in the pivot table then click "refresh".

    See attached. This macro will do it for you if you want to automate it. Put this code into the module for Sheet1(Invoice)

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Extrapolating spreadsheet data

    My non-macro solution.... in your other thread:

    http://www.excelforum.com/excel-gene...ml#post2558284


    Pivot table is the way to go, though.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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