Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table (Excele2000)

    My workbook has 3 sheets; 1 is the database, 2 is the Pivot Table, 3 is a spreadsheet that requires getting certain info. from the Pivot Table. Example, I'm looking for Total Number of Females and this number is in the Pivot Table in G100. On the 3rd sheet in the appropriate column, I type: =G100 and it returns the number, let's say 100. The problem is if the database changes which changes the pivot table and changes the location of the number. Example, I add a new employee in the database and now the total number is on G101. Now the 3rd sheet where I have the formula = G100 is not correct now. I tried GETPIVOTDATA, but must be doing something wrong so could someone explain how to use that in detail. Thank-you.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Excele2000)

    Hi Suzie:
    Please try to attach your workbook file. The process of answering your questions can be a lot more productive if we all are looking at the same spreadsheets. If your file excedes the size limit, you have two alternatives:
    1. Zip it first; or 2. save to another Workbook and copy the Pivot Table on top of itself using Paste, Special, Values. This should reduce the file size.
    Stephen

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Excele2000)

    OK. Attached is modified sample version of another type I'm having problems with. Go to first tab "Headcount" Column C to see where I'm picking up info. in the second tab.
    If you go to tab DB 8-06 and add a row, say add another employee under "Executive" then count of that cost center goes from 3 to 4; then refresh second tab "PT 8-06" and then go to first tab "Headcount" then the numbers are off.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Excele2000)

    Suzie:
    Your attachment did not attach or, at least, I can not see it. <img src=/S/clip.gif border=0 alt=clip width=15 height=15>
    Stephen

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Excele2000)

    Stephen, how do I attach a document? I'm having problems. I tried doing zip (not too familiar with this), but said too large. It's not that large a document. I tried just attaching it directly from C directory as document.xls but would not work -- try again later.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Excele2000)

    I thought you might not know but I was in a hurry. At the bottom of the window that you type into, under the "Edit/Create Signature" there is a window for entering the address of the file you want to attach. Click the Browse... button and it will pop open a "file Location" window that works the same as other applications like EXCEL or WORD. Make sure that you click the "Files of Type" button and specify EXCEL or whatever you want to send. Then click on the file and click "Open" and it shoud put yopur file name in the window.
    If it is larger than 100k, you will have to reduce it or Zip it or send it to me attached to an e-mail to sstollma@juno.com (filesize limitation here is 4 megabytes (4000K).
    Good Luck <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

  7. #7
    frog_hk
    Guest

    Re: Pivot Table (Excele2000)

    Hello Susie,

    To use the GETPIVOTDATA function use a syntax like this:
    =GETPIVOTDATA(Sheet1!F1:G100, "Female") <-note the quotation marks
    The first parameter being the range of the pivot table (without quotation mark)
    The second parameter being the NAME of the item (must have quotation mark, i.e. a String), which is the text in the cell on the left usually.

    Hope this helps <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Excele2000)

    OK Susie:
    I sent you back your file with the following note:
    Susie:
    I have attached what I believe to be a solution to your problem. The trick to getting the GETPIVOTDATA to work is to reverse the first two fields. These functions have a hard time when they get a number field when they were expecting a Text field. I also created an expandable Table called "Data_Table" (that is the name of the Named Range) and made that the source data for the Pivot Table. Now, each time your query changes the number of rows, the source will adjust to it. I had to save your file to XL97 so you may have to put back some lost formatting.
    Let me know if you need more help.
    Let me know if you did not get it.

Posting Permissions

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