+ Reply to Thread
Results 1 to 7 of 7

Calculating Totals from dynamic table

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    13

    Calculating Totals from dynamic table

    Hi Everyone,

    I have looked though this forum but i think i am facing a unique issue that i need your help with.

    In the attached file, there are two tabs, data and data summary. I want the data summary tab to pick up the yearly totals from the "Data" tab.

    However, there are two issues:

    1- the table in the Data tab varies from month to month (Columns and rows move, and are deleted/ added)
    2- The data i need is dependent on 3 variables: The Org, Currency and the total.

    For Example of Cell C9 of the data tab, i need excel to look at the table (which changes each month), narrow down org. 2100 and USD and then return the total for 2018.

    I would really appreciate your help with this! Thank you in advance!!

    Sohshak
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,118

    Re: Calculating Totals from dynamic table

    Your sheets don't match.
    Sheet "Data" has a single occurrence of each Org and Currency (one-to-one.)
    Sheet "Data Summary" has multiple repeat occurrences of each currency but NO occurrences of orgs.

    Therefore, there is no way to breakdown the currencies per org.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    06-19-2014
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: Calculating Totals from dynamic table

    Sorry, i forgot to add. Sometimes there are multiple occurrences of org and Currency whereas sometimes its one org and one currency.

    In this case, its possible for org. wth no listed currencies to equal zero through an iferror formula so that the data summary tab picks up the information it needs and zero's out the information that is not available?

    Thanks,

    sohshak

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,118

    Re: Calculating Totals from dynamic table

    The summary sheet must specify the org(s) somewhere. Please post a more representative example file. As it is, you can only match currency and year:
    Attached Files Attached Files
    Last edited by protonLeah; 04-15-2018 at 07:05 PM.

  5. #5
    Registered User
    Join Date
    06-19-2014
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: Calculating Totals from dynamic table

    Okay, find attached a more representative example spreadsheet.

    I have manually filled out the data summary tab for 2018 for the first two companies. I want to automate this process so that excel can recognize that Org. 2100 with currency of TLR has a 2018 total of 200.

    Please share your thoughts.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,118

    Re: Calculating Totals from dynamic table

    Dynamic Named Range:
    CurrencyByMonth =' DATA'!$A$8:INDEX(' DATA'!$1:$1048576,COUNTA(' DATA'!$A:$A)+4,COUNTA(' DATA'!$8:$8))

    Named constants just for readability
    CurrencyCol =2
    DateRow =1
    OrgCol =1

    Summary Formula:
    Please Login or Register  to view this content.
    With the new org data a pivot table works well.
    Attached Files Attached Files
    Last edited by protonLeah; 04-15-2018 at 07:56 PM.

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Calculating Totals from dynamic table

    another way you can use PowerQuery and PivotTable or simply even PivotTable

    Power Query for
    Last edited by sandy666; 04-15-2018 at 07:08 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

+ 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. [SOLVED] running totals in Pivot Table - removing a user from the totals for the current week
    By eisenbergg in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 09-08-2013, 04:39 PM
  2. Calculating totals in a table
    By jsuarez199 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2013, 10:01 AM
  3. Calculating Dynamic Table Value
    By roki4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2012, 02:43 PM
  4. Method for calculating Invoice Totals and Grand totals
    By sahran4441 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-06-2010, 10:24 PM
  5. Replies: 4
    Last Post: 01-10-2008, 12:31 PM
  6. Pivot Table Calculating totals differently
    By esparzaone in forum Excel General
    Replies: 1
    Last Post: 02-21-2006, 09:01 PM
  7. Calculating difference on Pivot Table Totals
    By PSM in forum Excel General
    Replies: 0
    Last Post: 01-12-2006, 11:40 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