Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot or consolidate (XP)

    Have a workbook with 52 sheets - each sheet has data totals - each sheet's data totals are totals of the SAME data on each sheet - just in different rows.

    I need some sort of "summary" of these totals - have never used consolidate tool and am pretty sure I have too much data for a pivot table - please help!

    Thanks again

    Aunt Linda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot or consolidate (XP)

    Can't you put all data on one sheet? That would make it a lot easier to summarize the data in various ways.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    No - this is one of those projects where at least 10 people want data from this workbook in 10 different ways - each sheet is a school within our school district - each sheet has to list every single space at each site, the sq ft of that space, what it's used for (1st grade, Library, etc.). At the bottom of each school's sheet I have to total the number of relocatable classrooms, the number of classrooms with grades 1-3, Kindergarten, Special Education, etc.

    This data is what I need summarized.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot or consolidate (XP)

    But if you merge the data from all those sheets into one master data sheet, you can still create all kinds of reports and summaries - per school, over schools, etc.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    That's a good idea - but I'm not sure how to keep each record identified with it's specific school site - I realize this is why all of this should be in Access - maybe I'll try exporting it that way - not real familiar with Access but I guess now is as good a time as any to learn!

    Any suggestions would be appreciated.

    Again, thanks

    Linda

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot or consolidate (XP)

    You could assign a unique number or code to each school (or you can use its name), and enter this in an extra column. That would identify the school a record belongs to.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    If nothing else, you could take the name form each sheet tab and put it in a column with the data from that sheet.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    That's what I've been doing - pain in the butt - but I guess since everyone's in the usual hurry, I'll just continue and try for a really cool fix later.

    Thank you Legare AND Hans - have a good one.

    Aunt Linda

  9. #9
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    As Hans suggested placing the data in the one worksheet would make life easier.

    You could then use the Autofilter to prepare "reports" for each school or department. If you don't have confidence in the users being able to use the autofilters you could consider using custom views or macros to control what is displayed.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    These are all excellent ideas - I'm attaching just ONE of the 82 sheets - one for each school - that I need summarized - at the bottom of the sheet is the calculated data I need summarized. Maybe this will spark some fantastic idea and I can wiggle my nose and it will all be done!

    Thanks for responding, Matthew - a friend has a friend visiting here (ugly Southern California) for a few months who lives in Sydney - she can't wait to get home!

    Aunt Linda
    Attached Files Attached Files

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot or consolidate (XP)

    I would use what was already suggested:
    I would create 1 master sheet with all of them on (add an additional column witht he school name) and use a pivot table to summarize. The school could be used as a page field.

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    That's exactly what I'm in the process of doing - any suggestions on how to speed the process up? Currently going back and forth from master sheet and school field to school field . . .

    Thanks for joining the fray - Live Long and Prosper

    Aunt Linda

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot or consolidate (XP)

    In the attached example I have placed the data for two schools below each other (by simply copying and pasting), and added columns for school and year.
    The second sheet shows a summary using SUMPRODUCT formulas.
    The third sheet shows a simple pivot table. You can select a school and year in the page fields near the top.
    Attached Files Attached Files

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot or consolidate (XP)

    I did begin that process as well but wouldn't have thought to put the school year - don't have a lot of experience with pivot tables but, thanks to you, I 'reckon I'll be learnin'!

    You rock, Hans.

    Thank you, again

    Aunt Linda

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot or consolidate (XP)

    You could create a macro (modify the code in <post:=588,649>post 588,649</post:> ?)

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •