1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Pivot multiple sheets for text, date and decimal fields

Discussion in 'Business Applications' started by RoslynA, Oct 4, 2012.

Thread Status:
Not open for further replies.
  1. RoslynA

    RoslynA Thread Starter

    Oct 3, 2012
    EXCEL SOS!!! I have an urgent deadline which I'm likely to fail if I can't get my head around this issue so please please feel free to comment / offer suggestions...I'll try anything!

    I have around 10 - 15 sheets within the same excle file which is effectively acting as one tab per resource within my consultancy team. These are acting a a record of the billable & non billable hours per resource and therefore each line is primarily driven by date (2011 - 2013), with 1:N ratio items of the following: Client, Contract, Role, Days & hrs (effectively 1 line per date, resource, client & contract combination + time billed within the std 8 hr working day).

    I have no problems creating individual pivot tables in the individual resource name's relevant tab, however the next step is to roll this information togather to show a holistic view for all resources, all clients etc (in what I anticipated to be a pivot table via the consolidation of multiple ranges function) for all resources by exactly the same fields:

    Year, Date, Contract/Type, Resource, & Date - at row level (with possible additional options of Month, Week, Day being added as a row label)


    CLIENT , Non Billable - at column level

    Naturally there may well be some slicing and dicing of the info needed to understand the figures at different levels, ie per resource (days billed & rate), how many hours used per contract, how we've used time in a contract (by role), & whom (by resource) per client, and time range (Annually, Qtrly, H1/H2, Monthly etc).

    I've followed numerous you tube videos on how to perform the consolidation from mutlpile data sources (ie the multiple tabs) to try and understand what I'm doing wrong, from both the raw data lists and the individual pivots that are created correctly at one tab/resource level, but I just cannot get the data to display correctly (grr!). There are 3 things I've noticed that suspect may be causing the problem:

    1) I am limited to 4 page fields and although I've researched into what these are exactly I only get vague explanations (East / West tab) if any, and no real context to help understand how that could possibly translate to my situation. I've tried using Client, Contract, Resource, Day as page fields but this doesn't bring any values back in the final pivot...but when I set page field 1 as the resource name (ie as per the tab structure) and move the first column of each sheet to contain the column with the resource name, it at least shows the underlying values of the resource/tab names but nothing else.

    2) All the examples on you tube are all purely figures and therefore SUM correctly. My data is a combination of text, date and figures I'd like to sum. As I only ever get COLUMN, ROW, and Page Fields 1 - 4 (dependant on how many I've specified) I cannot break either the rows or the columnal underlying data out...

    3) I can't seem to get the row level structure that I can get in the individual sheet pivot tables...No idea why!

    I apologies for the long explanation but as I'm sure you can appreciate, as this is relatively confidential data I'm not in a position to provide the file easily, so I've sent a screenshot or two that hopefully will outline the pivot I have successfully acheived along with what I'm seeing on the consolidated pivot view, in addition to the above explanation..

    Any help you can provide would be VERY VERY much appreciated!

    Attached Files:

  2. Zack Barresse

    Zack Barresse

    Jul 25, 2004
    Hi there, welcome to the board!

    I feel your frustration. Unfortunately your data structure isn't conducive for a PivotTable data model. But there is hope! You have some options here. First of all we need to know what version you're using. If you have 2010, well, you're going to really like what I'm about to tell you, and if you don't have it, it's a good reason to upgrade! 2010 has a new add-in called PowerPivot. Think of it as PivotTable's on bulk steroids. The benefit you would see - the ability to select multiple data sources. This means you can have multiple data tables/sheets as your data source, and select their relationships. Think of it like an Access table/relationship. It's the best feature we've seen in Excel since the PivotTable itself.

    So if you have 2010, this is good news. However, if you don't have 2010, we need to consolidate your data to a single data structure. Whether that is putting it all on one sheet, or exporting to Access, or whatever, that's what needs to happen.

    I'm not entirely sure what you mean about not getting the row level structure. Is there any way you can post the file?
  3. RoslynA

    RoslynA Thread Starter

    Oct 3, 2012
    Hi Zack,

    Apologies for the delay in responding. I'm still quite new to the site and found it a little challenging finding my thread to be honest ;-)

    In the meantime however, I did the classic "revert to what you know" and imported each resource/sheet into Access and have built a few queries/reports off there to fit my need. Unfortunately I can't change the structure/format of the underlying sheets but I am on 2010 so I'll start playing with Power pivot when I can get a few minutes downtime and see what I can come up with (hopefully a big smile to replace the recent weeks frown lines)!

    Thanks again,
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1071357

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice