+ Reply to Thread
Results 1 to 3 of 3

Formulas to consolidate data

  1. #1
    Forum Contributor
    Join Date
    Costa Rica
    MS-Off Ver
    MS Excel 365

    Formulas to consolidate data

    Hello there!

    I need someone to help, please!

    As of now, I have to consolidate or put together data from more than 20 worksheets on the same workbook, in order to make a pivot table with chart that goes on a weekly report I have to hand out to my boss. I need to find a way of making the consolidation process automatic because data will update every week, and I simply don’t visualize myself manually copying and pasting data from all these worksheets into a master tab (the consolidation tab).

    I have enclosed a file to show you how I have data organized on my worksheets (See tabs ITEM1, ITEM2, and ITEM3), and what I’d love to obtain with your help (See CONSOLIDATED ITEMS tab to see how I’d like to have data automatically put together to be able to make the pivot and chart found there). The ITEM tabs have 4 columns: the first one is for checkpoints or dates; the second one stands for countries; the third one is for items (say, for instance, # of clients, # of agents or salespersons, units of product sold, etc.), and lastly, a column for values.

    The reason why I have put items all together on a same column rather than putting all of them on separate ones as someone would normally do, is because I need to be able to chart my pivot so that all items be shown one at a time and not all together at once (see chart on CONSOLIDATED ITEMS tab).

    Making a consolidated pivot hasn’t worked for me since I lose columns content when doing the consolidation (See consolidated pivot attached on CONSOLIDATED ITEMS tab).

    I don’t think Excel’s consolidate feature could work either since data will be different every week (not every country and every item will show up on the ITEMS tabs every week); so data’s format is not consistent.

    So, is there a way to do this? What about using array formulas so that data can be automatically updated on the master tab? I don’t have any clue of how to do this.
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365

    Re: Formulas to consolidate data

    VBA is the way to accomplish this. Assuming that you must collect data from EVERY sheet in the workbook except the CONSOLIDATE items sheet, do this:

    1) Right-click on the CONSOLIDATED ITEMS tab and select View Code, the VBA editor will open that sheet module for you
    2) Paste in this "Event" macro into the module that appears:

    Please Login or Register  to view this content.
    3) Close the VBEditor and save your workbook.

    Now, everytime you bring CONSOLIDATED ITEMS up onscreen the data will refresh, then your two pivot tables will refresh. Since the Chart is connected to them, it will refresh itself.
    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!)

  3. #3
    Forum Contributor
    Join Date
    Costa Rica
    MS-Off Ver
    MS Excel 365

    Re: Formulas to consolidate data

    hi JBeaucaire!

    thanks for your reply, really!

    Can you do me a favor! Would you be able to modify your code so as to consider this time the way I have reorganized items on my file? See the image below.

    Change location of items.JPG

    I guess is much easier to have all items shown on a same tab rather than having multiple tabs. Also, I have excluded from your code the pivots refreshing. Your code contains the number of the pivots from the former file attached to my post, but this is not the file I use to generate the consolidation. The file I will use to generate my report for my boss has other pivots too, so using your code will produce an error. So, just tell me how to modify your code so the search for data to consolidate does not take place in multiple tabs as shown by your code, but only in one tab. I need to see how you tell the code to search exactly where on the file, so I can do it later on the file I will use to perform the real consolidation.
    Last edited by alchavar; 10-26-2013 at 10:52 PM.

+ 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. Consolidate formulas pointing to other formulas into one
    By weeble33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2013, 12:22 PM
  2. [SOLVED] Consolidate data from workbooks and create link formulas in the summary workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2013, 04:42 PM
  3. consolidate data from multiple files/worksheets when data in variable location
    By Aaron_Tram in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2013, 12:49 PM
  4. Replies: 4
    Last Post: 12-03-2010, 09:38 AM
  5. Merge (consolidate) data from multiple workbooks with values derived from formulas
    By Hester's Dad in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-25-2010, 11:36 AM


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