+ Reply to Thread
Results 1 to 4 of 4

GETPIVOTDATA using a reference for field name

  1. #1
    Rayo K
    Guest

    GETPIVOTDATA using a reference for field name

    Can this be done. I read some posts suggesting it could, but I get a REF
    error. Here is my formula:

    =GETPIVOTDATA(B20,BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)

    B20 is a cell containing:

    =CONCATENATE("M ",A20," KPH")

    and A20 is a three digit number.

    "M 122 KPH" is an example. This is a calculated field. I am using Windows XP
    and Office 2003.

    It works when I enter the field name directly so it is almost certainly the
    source of the error.

  2. #2
    Barb Reinhardt
    Guest

    Re: GETPIVOTDATA using a reference for field name

    Have you tried

    =GETPIVOTDATA(indirect(B20),BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)


    "Rayo K" <[email protected]> wrote in message
    news:[email protected]...
    > Can this be done. I read some posts suggesting it could, but I get a REF
    > error. Here is my formula:
    >
    > =GETPIVOTDATA(B20,BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)
    >
    > B20 is a cell containing:
    >
    > =CONCATENATE("M ",A20," KPH")
    >
    > and A20 is a three digit number.
    >
    > "M 122 KPH" is an example. This is a calculated field. I am using Windows
    > XP
    > and Office 2003.
    >
    > It works when I enter the field name directly so it is almost certainly
    > the
    > source of the error.




  3. #3
    Debra Dalgleish
    Guest

    Re: GETPIVOTDATA using a reference for field name

    Add an empty string to the B20 reference:


    =GETPIVOTDATA(B20&"",BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)

    Rayo K wrote:
    > Can this be done. I read some posts suggesting it could, but I get a REF
    > error. Here is my formula:
    >
    > =GETPIVOTDATA(B20,BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)
    >
    > B20 is a cell containing:
    >
    > =CONCATENATE("M ",A20," KPH")
    >
    > and A20 is a three digit number.
    >
    > "M 122 KPH" is an example. This is a calculated field. I am using Windows XP
    > and Office 2003.
    >
    > It works when I enter the field name directly so it is almost certainly the
    > source of the error.



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


  4. #4
    Rayo K
    Guest

    Re: GETPIVOTDATA using a reference for field name

    Thanks. It actually didn't work, but I decided that I wouldn't need the
    references to change so I just typed them in.

    "Debra Dalgleish" wrote:

    > Add an empty string to the B20 reference:
    >
    >
    > =GETPIVOTDATA(B20&"",BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)
    >
    > Rayo K wrote:
    > > Can this be done. I read some posts suggesting it could, but I get a REF
    > > error. Here is my formula:
    > >
    > > =GETPIVOTDATA(B20,BoardPivot!$A$3,"Shift",D20,"Year",Calculations!$B$15,"Month",Calculations!$B$14)
    > >
    > > B20 is a cell containing:
    > >
    > > =CONCATENATE("M ",A20," KPH")
    > >
    > > and A20 is a three digit number.
    > >
    > > "M 122 KPH" is an example. This is a calculated field. I am using Windows XP
    > > and Office 2003.
    > >
    > > It works when I enter the field name directly so it is almost certainly the
    > > source of the error.

    >
    >
    > --
    > 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