Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Thanked 0 Times in 0 Posts

    Stymied By PivotTable (2000)

    I'm playing around with a PivotTable that uses mult. consolidation ranges. Each table (consolidation range) is identical in structure: first column contains years, from 1998 through 2004. The columns are labeled with month names. The last two columns in each table are "YTD" and "Mo. Average". A value appears at each intersection of year and month.

    I build the PivotTable, consolidating all of these tables - 8 in total. Got it all tricked out with three page fields, etc. All the values (SUMs in this case) are accurate. Sweet.

    Here's the part I don't get: the months, running along the top of the PivotTable, are in random order. Every time I refresh the data, the months seem to randomly switch places. The order is neither alphabetical (April, February, etc.) nor based on any value in the table, AFAIK. It seems completely and utterly random.

    Right now the order of the months is:
    Jan, March, Feb, Aug, April, May, June, July, Oct, Nov, Dec, Mo. Avg., Sept, YTD.

    Now, having made no changes to the data, I'll refresh the data. Done. Now the order is:
    Jan,Feb, Mar, Aug, Oct, Nov, Dec, April, May, June, July, Mo. Avg., Sept., YTD.

    Want to play another round? Sure - maybe this time I'll hit triple bar and this sucker will pay out!
    Jan,Feb, Mar, Apr, May, June, July, Aug, Oct, Nov, Dec, Mo. Avg., Sept, YTD

    Any ideas what's going on? I thought it might have something to do with the "YTD" and "Mo. Avg" fields but I don't know if that explains, to me, why this would create a slightly different order every time.

    All suggestions greatly appreciated. And since this is my first significant foray into PivotTables using mult. consolidation ranges, it is entirely possible I've just messed up something fundamental and don't know it.

    File is attached.


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Stymied By PivotTable (2000)

    Change the names in all sheets to:
    Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
    Or spell them all out completely and they will be sorted by month.

    Excel gets confused when you mix formats of months as text. It starts treating some as text others as months and it just doesn't know what to... do.


Posting Permissions

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