+ Reply to Thread
Results 1 to 6 of 6

Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

  1. #1
    Registered User
    Join Date
    06-21-2011
    Location
    LA, CA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Question Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

    Hi all! new to the forum and have some questions regarding a dashboard I'm trying to set up.

    What I am trying to accomplish is a scatter plot using pivot charts so that I can specify the data down to the specific floor and unit (dealing with malls).

    Easiest way off course is to have 1 raw database which I can do, but each month of the tenant data I use takes up 600 lines and my boss thinks that it will take up too much memory in the long run (true or not?) as there are 8 malls to consider.

    What I am trying to do now is have a workbook for each mall with 1 year's worth of data on each sheet but I am not sure how or if I would be able to connect all these sheets an workbooks back to my dashboard scatter plot chart

    So far, I have made a dynamic scatter plot with drop box options to change the data based on year, month, and floor based on a pivot chart but this is only for one mall. (Screens attached)

    Please let me know what you think and if you have other suggestions to setting this up!



    Hope someone answers me this time :o just looking for guidance
    Attached Images Attached Images
    Last edited by woontime; 07-01-2011 at 01:43 AM.

  2. #2
    Registered User
    Join Date
    06-21-2011
    Location
    LA, CA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Multiple Sheets Into One Pivot table (Not Consolidating Pivot Wizard) Dashboard

    UPDATE: So it seems like one viable option for what I want to do is to use a macro to copy all sheets in a workbook to a master sheet in that workbook. The master workbook is cleared and updated everytime macro is run. But with my data/memory in each sheet how long til the work book gets too big?

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

    Hi woontime,

    I've got a table of over 60,000 rows and Pivots work GREAT!! 600 more rows a month is not a problem as this is 7200 rows a year. You said 8 malls times 100 tenets(?) times 600 rows times 12 months = 5.5 million rows. I think you are good keeping your data in a single sheet up to 100,000 rows without much concern. Excel 2007 and 2010 claim over 1 million rows.

    I'd start gathering all the data on a single sheet until it gets so big it no longer makes sense. It is much easier to split it into different Malls than to join different workbooks into a single one.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-21-2011
    Location
    LA, CA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

    Thanks for the response MarvinP,

    So it's not organized my tenents but rather by units so roughly 600 units with 450-500 tenents (as they can occupy 1 or more units)

    The data (workbook) will not run slow after say 3-5 years?

    I've decided to run a macro where each mall will have its own workbook and update the master worksheet on that page. So each subsequent sheet to the master will have a year's worth of data.

    So each month there will be 1200 rows added (600 each sheet) * 12 = 14,400 rows/yr and there are 20 columns.

    Also keeping in mind that the end product is linking this data to another workbook where we can run a small dashboard for reporting

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

    Hi woontime,
    If you get real excited and want to learn the latest (and upgrade to 2010 Excel) Microsoft released an answer that I think meets your problem. Look at:
    http://www.powerpivot.com/

    I've downloaded and installed it, as it is free, but have not needed to learn or use it yet.

  6. #6
    Registered User
    Join Date
    06-21-2011
    Location
    LA, CA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

    haha thanks MarvinP,

    looks like powerful stuff. To bad my company only uses 2007 but definitely something I would love to play around with sometime in the future

+ 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