+ Reply to Thread
Results 1 to 9 of 9

Pivot Tables and the getpivotdata formula

  1. #1
    Registered User
    Join Date
    09-06-2005
    Posts
    1

    Exclamation Pivot Tables and the getpivotdata formula

    I use a pivottable to organise data in a list and then use the getpivotdata formula to pull the data into a customised report.

    I have run into a problem now where I try to copy the file and create a new one for the present week. I change the source data however the getpivotdata formulas all now show ref! error. I've checked the formula and see nothing wrong. Can anyone help me? please copy your response to [email protected]

  2. #2
    Debra Dalgleish
    Guest

    Re: Pivot Tables and the getpivotdata formula

    Does the pivot table still include the cell that's referenced in the
    GetPivotData formula?
    Have the field or item names changed? Maybe they include a trailing
    space that wasn't there before.

    Maxxin wrote:
    > I use a pivottable to organise data in a list and then use the
    > getpivotdata formula to pull the data into a customised report.
    >
    > I have run into a problem now where I try to copy the file and create a
    > new one for the present week. I change the source data however the
    > getpivotdata formulas all now show ref! error. I've checked the formula
    > and see nothing wrong. Can anyone help me? please copy your response to
    > [email protected]
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  3. #3
    Jeff K
    Guest

    Re: Pivot Tables and the getpivotdata formula

    I am seeing the same kind of problem. I have a monthly file which I resave
    each month as a different name. All of the sudden on the most recent resave,
    my getpivotdata formulas no longer work, with absolutely no changes to the
    file other than the filename. Any suggestions out there?

    "Debra Dalgleish" wrote:

    > Does the pivot table still include the cell that's referenced in the
    > GetPivotData formula?
    > Have the field or item names changed? Maybe they include a trailing
    > space that wasn't there before.
    >
    > Maxxin wrote:
    > > I use a pivottable to organise data in a list and then use the
    > > getpivotdata formula to pull the data into a customised report.
    > >
    > > I have run into a problem now where I try to copy the file and create a
    > > new one for the present week. I change the source data however the
    > > getpivotdata formulas all now show ref! error. I've checked the formula
    > > and see nothing wrong. Can anyone help me? please copy your response to
    > > [email protected]
    > >
    > >

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >
    >


  4. #4
    Debra Dalgleish
    Guest

    Re: Pivot Tables and the getpivotdata formula

    What version of Excel are you using?
    What's the GetPivotData formula?
    What was the file name previously, and what is the new file name?

    Jeff K wrote:
    > I am seeing the same kind of problem. I have a monthly file which I resave
    > each month as a different name. All of the sudden on the most recent resave,
    > my getpivotdata formulas no longer work, with absolutely no changes to the
    > file other than the filename. Any suggestions out there?
    >
    > "Debra Dalgleish" wrote:
    >
    >
    >>Does the pivot table still include the cell that's referenced in the
    >>GetPivotData formula?
    >>Have the field or item names changed? Maybe they include a trailing
    >>space that wasn't there before.
    >>
    >>Maxxin wrote:
    >>
    >>>I use a pivottable to organise data in a list and then use the
    >>>getpivotdata formula to pull the data into a customised report.
    >>>
    >>>I have run into a problem now where I try to copy the file and create a
    >>>new one for the present week. I change the source data however the
    >>>getpivotdata formulas all now show ref! error. I've checked the formula
    >>>and see nothing wrong. Can anyone help me? please copy your response to
    >>>[email protected]
    >>>
    >>>

    >>
    >>
    >>--
    >>Debra Dalgleish
    >>Excel FAQ, Tips & Book List
    >>http://www.contextures.com/tiptech.html
    >>
    >>

    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  5. #5
    Jeff K
    Guest

    Re: Pivot Tables and the getpivotdata formula

    This is in Excel 2000. The file was previously "Deposit Summary - Oct
    05.xls" and is now "Deposit Summary - Nov 05.xls"

    one of the formulas is:
    =GETPIVOTDATA(tables!$BH$3,Haberfeld!G$6&" eom")

    "Debra Dalgleish" wrote:

    > What version of Excel are you using?
    > What's the GetPivotData formula?
    > What was the file name previously, and what is the new file name?
    >
    > Jeff K wrote:
    > > I am seeing the same kind of problem. I have a monthly file which I resave
    > > each month as a different name. All of the sudden on the most recent resave,
    > > my getpivotdata formulas no longer work, with absolutely no changes to the
    > > file other than the filename. Any suggestions out there?
    > >
    > > "Debra Dalgleish" wrote:
    > >
    > >
    > >>Does the pivot table still include the cell that's referenced in the
    > >>GetPivotData formula?
    > >>Have the field or item names changed? Maybe they include a trailing
    > >>space that wasn't there before.
    > >>
    > >>Maxxin wrote:
    > >>
    > >>>I use a pivottable to organise data in a list and then use the
    > >>>getpivotdata formula to pull the data into a customised report.
    > >>>
    > >>>I have run into a problem now where I try to copy the file and create a
    > >>>new one for the present week. I change the source data however the
    > >>>getpivotdata formulas all now show ref! error. I've checked the formula
    > >>>and see nothing wrong. Can anyone help me? please copy your response to
    > >>>[email protected]
    > >>>
    > >>>
    > >>
    > >>
    > >>--
    > >>Debra Dalgleish
    > >>Excel FAQ, Tips & Book List
    > >>http://www.contextures.com/tiptech.html
    > >>
    > >>

    > >

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >
    >


  6. #6
    Jeff K
    Guest

    Re: Pivot Tables and the getpivotdata formula

    I found the issue, just not sure why it's popped up or how to fix it. In the
    formula below if the Haberfeld!G6 cell is text, the formula works, but does
    not if this cell is numeric. Any suggestions?

    "Jeff K" wrote:

    > This is in Excel 2000. The file was previously "Deposit Summary - Oct
    > 05.xls" and is now "Deposit Summary - Nov 05.xls"
    >
    > one of the formulas is:
    > =GETPIVOTDATA(tables!$BH$3,Haberfeld!G$6&" eom")
    >
    > "Debra Dalgleish" wrote:
    >
    > > What version of Excel are you using?
    > > What's the GetPivotData formula?
    > > What was the file name previously, and what is the new file name?
    > >
    > > Jeff K wrote:
    > > > I am seeing the same kind of problem. I have a monthly file which I resave
    > > > each month as a different name. All of the sudden on the most recent resave,
    > > > my getpivotdata formulas no longer work, with absolutely no changes to the
    > > > file other than the filename. Any suggestions out there?
    > > >
    > > > "Debra Dalgleish" wrote:
    > > >
    > > >
    > > >>Does the pivot table still include the cell that's referenced in the
    > > >>GetPivotData formula?
    > > >>Have the field or item names changed? Maybe they include a trailing
    > > >>space that wasn't there before.
    > > >>
    > > >>Maxxin wrote:
    > > >>
    > > >>>I use a pivottable to organise data in a list and then use the
    > > >>>getpivotdata formula to pull the data into a customised report.
    > > >>>
    > > >>>I have run into a problem now where I try to copy the file and create a
    > > >>>new one for the present week. I change the source data however the
    > > >>>getpivotdata formulas all now show ref! error. I've checked the formula
    > > >>>and see nothing wrong. Can anyone help me? please copy your response to
    > > >>>[email protected]
    > > >>>
    > > >>>
    > > >>
    > > >>
    > > >>--
    > > >>Debra Dalgleish
    > > >>Excel FAQ, Tips & Book List
    > > >>http://www.contextures.com/tiptech.html
    > > >>
    > > >>
    > > >

    > >
    > >
    > > --
    > > Debra Dalgleish
    > > Excel FAQ, Tips & Book List
    > > http://www.contextures.com/tiptech.html
    > >
    > >


  7. #7
    Debra Dalgleish
    Guest

    Re: Pivot Tables and the getpivotdata formula

    Try using the TEXT function in the formula, e.g.:

    =GETPIVOTDATA(tables!$BH$3,"'" &TEXT(Haberfeld!G$6,"0")&" eom'")

    Jeff K wrote:
    > I found the issue, just not sure why it's popped up or how to fix it. In the
    > formula below if the Haberfeld!G6 cell is text, the formula works, but does
    > not if this cell is numeric. Any suggestions?
    >
    > "Jeff K" wrote:
    >
    >
    >>This is in Excel 2000. The file was previously "Deposit Summary - Oct
    >>05.xls" and is now "Deposit Summary - Nov 05.xls"
    >>
    >>one of the formulas is:
    >>=GETPIVOTDATA(tables!$BH$3,Haberfeld!G$6&" eom")
    >>
    >>"Debra Dalgleish" wrote:
    >>
    >>
    >>>What version of Excel are you using?
    >>>What's the GetPivotData formula?
    >>>What was the file name previously, and what is the new file name?
    >>>
    >>>Jeff K wrote:
    >>>
    >>>>I am seeing the same kind of problem. I have a monthly file which I resave
    >>>>each month as a different name. All of the sudden on the most recent resave,
    >>>>my getpivotdata formulas no longer work, with absolutely no changes to the
    >>>>file other than the filename. Any suggestions out there?
    >>>>
    >>>>"Debra Dalgleish" wrote:
    >>>>
    >>>>
    >>>>
    >>>>>Does the pivot table still include the cell that's referenced in the
    >>>>>GetPivotData formula?
    >>>>>Have the field or item names changed? Maybe they include a trailing
    >>>>>space that wasn't there before.
    >>>>>
    >>>>>Maxxin wrote:
    >>>>>
    >>>>>
    >>>>>>I use a pivottable to organise data in a list and then use the
    >>>>>>getpivotdata formula to pull the data into a customised report.
    >>>>>>
    >>>>>>I have run into a problem now where I try to copy the file and create a
    >>>>>>new one for the present week. I change the source data however the
    >>>>>>getpivotdata formulas all now show ref! error. I've checked the formula
    >>>>>>and see nothing wrong. Can anyone help me? please copy your response to
    >>>>>>[email protected]
    >>>>>>
    >>>>>>
    >>>>>
    >>>>>
    >>>>>--
    >>>>>Debra Dalgleish
    >>>>>Excel FAQ, Tips & Book List
    >>>>>http://www.contextures.com/tiptech.html
    >>>>>
    >>>>>
    >>>>
    >>>
    >>>--
    >>>Debra Dalgleish
    >>>Excel FAQ, Tips & Book List
    >>>http://www.contextures.com/tiptech.html
    >>>
    >>>

    >>



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  8. #8
    Jeff K
    Guest

    Re: Pivot Tables and the getpivotdata formula

    Yes, I thought this would work as well, but no success. No idea why it all
    of the sudden has a problem with the numeric values, this always worked until
    this week.

    "Debra Dalgleish" wrote:

    > Try using the TEXT function in the formula, e.g.:
    >
    > =GETPIVOTDATA(tables!$BH$3,"'" &TEXT(Haberfeld!G$6,"0")&" eom'")
    >
    > Jeff K wrote:
    > > I found the issue, just not sure why it's popped up or how to fix it. In the
    > > formula below if the Haberfeld!G6 cell is text, the formula works, but does
    > > not if this cell is numeric. Any suggestions?
    > >
    > > "Jeff K" wrote:
    > >
    > >
    > >>This is in Excel 2000. The file was previously "Deposit Summary - Oct
    > >>05.xls" and is now "Deposit Summary - Nov 05.xls"
    > >>
    > >>one of the formulas is:
    > >>=GETPIVOTDATA(tables!$BH$3,Haberfeld!G$6&" eom")
    > >>
    > >>"Debra Dalgleish" wrote:
    > >>
    > >>
    > >>>What version of Excel are you using?
    > >>>What's the GetPivotData formula?
    > >>>What was the file name previously, and what is the new file name?
    > >>>
    > >>>Jeff K wrote:
    > >>>
    > >>>>I am seeing the same kind of problem. I have a monthly file which I resave
    > >>>>each month as a different name. All of the sudden on the most recent resave,
    > >>>>my getpivotdata formulas no longer work, with absolutely no changes to the
    > >>>>file other than the filename. Any suggestions out there?
    > >>>>
    > >>>>"Debra Dalgleish" wrote:
    > >>>>
    > >>>>
    > >>>>
    > >>>>>Does the pivot table still include the cell that's referenced in the
    > >>>>>GetPivotData formula?
    > >>>>>Have the field or item names changed? Maybe they include a trailing
    > >>>>>space that wasn't there before.
    > >>>>>
    > >>>>>Maxxin wrote:
    > >>>>>
    > >>>>>
    > >>>>>>I use a pivottable to organise data in a list and then use the
    > >>>>>>getpivotdata formula to pull the data into a customised report.
    > >>>>>>
    > >>>>>>I have run into a problem now where I try to copy the file and create a
    > >>>>>>new one for the present week. I change the source data however the
    > >>>>>>getpivotdata formulas all now show ref! error. I've checked the formula
    > >>>>>>and see nothing wrong. Can anyone help me? please copy your response to
    > >>>>>>[email protected]
    > >>>>>>
    > >>>>>>
    > >>>>>
    > >>>>>
    > >>>>>--
    > >>>>>Debra Dalgleish
    > >>>>>Excel FAQ, Tips & Book List
    > >>>>>http://www.contextures.com/tiptech.html
    > >>>>>
    > >>>>>
    > >>>>
    > >>>
    > >>>--
    > >>>Debra Dalgleish
    > >>>Excel FAQ, Tips & Book List
    > >>>http://www.contextures.com/tiptech.html
    > >>>
    > >>>
    > >>

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >
    >


  9. #9
    Debra Dalgleish
    Guest

    Re: Pivot Tables and the getpivotdata formula

    Are you sure the values are the same? Maybe one value contains hidden
    character, such as extra spaces.

    Jeff K wrote:
    > Yes, I thought this would work as well, but no success. No idea why it all
    > of the sudden has a problem with the numeric values, this always worked until
    > this week.
    >
    > "Debra Dalgleish" wrote:
    >
    >
    >>Try using the TEXT function in the formula, e.g.:
    >>
    >> =GETPIVOTDATA(tables!$BH$3,"'" &TEXT(Haberfeld!G$6,"0")&" eom'")
    >>
    >>Jeff K wrote:
    >>
    >>>I found the issue, just not sure why it's popped up or how to fix it. In the
    >>>formula below if the Haberfeld!G6 cell is text, the formula works, but does
    >>>not if this cell is numeric. Any suggestions?
    >>>
    >>>"Jeff K" wrote:
    >>>
    >>>
    >>>
    >>>>This is in Excel 2000. The file was previously "Deposit Summary - Oct
    >>>>05.xls" and is now "Deposit Summary - Nov 05.xls"
    >>>>
    >>>>one of the formulas is:
    >>>>=GETPIVOTDATA(tables!$BH$3,Haberfeld!G$6&" eom")
    >>>>
    >>>>"Debra Dalgleish" wrote:
    >>>>
    >>>>
    >>>>
    >>>>>What version of Excel are you using?
    >>>>>What's the GetPivotData formula?
    >>>>>What was the file name previously, and what is the new file name?
    >>>>>
    >>>>>Jeff K wrote:
    >>>>>
    >>>>>
    >>>>>>I am seeing the same kind of problem. I have a monthly file which I resave
    >>>>>>each month as a different name. All of the sudden on the most recent resave,
    >>>>>>my getpivotdata formulas no longer work, with absolutely no changes to the
    >>>>>>file other than the filename. Any suggestions out there?
    >>>>>>
    >>>>>>"Debra Dalgleish" wrote:
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>>Does the pivot table still include the cell that's referenced in the
    >>>>>>>GetPivotData formula?
    >>>>>>>Have the field or item names changed? Maybe they include a trailing
    >>>>>>>space that wasn't there before.
    >>>>>>>
    >>>>>>>Maxxin wrote:
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>>I use a pivottable to organise data in a list and then use the
    >>>>>>>>getpivotdata formula to pull the data into a customised report.
    >>>>>>>>
    >>>>>>>>I have run into a problem now where I try to copy the file and create a
    >>>>>>>>new one for the present week. I change the source data however the
    >>>>>>>>getpivotdata formulas all now show ref! error. I've checked the formula
    >>>>>>>>and see nothing wrong. Can anyone help me? please copy your response to
    >>>>>>>>[email protected]
    >>>>>>>>
    >>>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>--
    >>>>>>>Debra Dalgleish
    >>>>>>>Excel FAQ, Tips & Book List
    >>>>>>>http://www.contextures.com/tiptech.html
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>--
    >>>>>Debra Dalgleish
    >>>>>Excel FAQ, Tips & Book List
    >>>>>http://www.contextures.com/tiptech.html
    >>>>>
    >>>>>
    >>>>

    >>
    >>--
    >>Debra Dalgleish
    >>Excel FAQ, Tips & Book List
    >>http://www.contextures.com/tiptech.html
    >>
    >>

    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1