Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Consolidation Ranges (2003)

    I am trying to create a Pivot table using multiple consolidation ranges.
    I have defined named ranges in 2 workbooks
    I have the first workbook open.
    I choose Data
    Pivot table and Pivot chart Report
    I select Multiple Consolidation Ranges
    Accepted the Pivot table option
    choose next
    Accept first option
    Next
    for the range, I type in the named range in the open workbook
    Choose add
    type in the second named range and choose add

    Now my problem:
    I select Browse to go out to the other workbook
    I select the workbook and click OK and it adds the path to the second workbook where it says range
    Now I want to get to the end of the pathname to enter in the first named range that is in this second workbook
    I can't use the arrow keys to get to the end of the pathname.
    The left arrow key gets rid of the pathname and the right arrow key adds "January!$C$2" whereever I have the cursor in the pathname.
    What am I doing wrong or how do I use this feature?
    Thanks for any help you can provide.

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

    Re: Multiple Consolidation Ranges (2003)

    You'll have to use the mouse to go to the end of the path. (Yes, it's annoying!)

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Multiple Consolidation Ranges (2003)

    If you press f2 before you use the arrow keys, you should be OK.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Consolidation Ranges (2003)

    I used the mouse and got to the end of the pathname and after the "!", I put the named range. I put the second named range in and added it. When I click on next, it says "Reference is not valid". My named ranges look fine. What could I being doing wrong?

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

    Re: Multiple Consolidation Ranges (2003)

    I think we'd have to see the workbooks involved. You can replace sensitive data with dummy data.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Consolidation Ranges (2003)

    Problem Solved. I went back and checked all my named ranges and there was one that wasn't defined. I must not have saved it once I created the named range. Thanks for all your help.

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

    Re: Multiple Consolidation Ranges (2003)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Glad it's solved!

Posting Permissions

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