none
Automatically fill a cell with the number from a separate pivot table

    Question

  • Hi,

    I have a sheet with data, a sheet with a pivot table which analyses this data and another sheet which shows the results.

    How can I automatically fill in cells with results from the pivot table?

    I have department codes in column A and I want to return the results for whether there are any new employees in that department in the same row in column G. The pivot table shows the number of new employees, arranged by department. Department code is in column B and total of new employees show is column E. I just want to pull that number across by matching the dept codes.

    Thanks

    Thursday, March 08, 2012 8:47 AM

Answers

  • VLOOKUP does work, in my experience. You need to have the exact value in A2 that matches a value in column B of your pivot table.

    If you want to use GETPIVOTDATA, simply type an equal sign, then navigate to your pivot table and click the cell with the data that you want. You will get a formula that looks like this:

    =GETPIVOTDATA("Field 5",PTable!$A$3,"Field 1","Value 1","Field 3","Value 3","Field 4","Value 4")

    The length depends on the number of fields that you've used. If you want, any of the Values can be replaced with cell references that contain valid values, so that the formula can be copied down or across to pull multiple values for a sub table.


    HTH, Bernie

    Thursday, March 08, 2012 5:17 PM

All replies

  • In G2, enter the formula

    =VLOOKUP(A2,'Pivot Table Sheet'!B:E,4,FALSE)

    Change the sheet name as appropriate.

    Bernie


    HTH, Bernie

    Thursday, March 08, 2012 2:54 PM
  • VLOOKUP's not working. I think I need =GETPIVOTDATA but I'm having trouble writing the formula?
    Thursday, March 08, 2012 4:31 PM
  • VLOOKUP does work, in my experience. You need to have the exact value in A2 that matches a value in column B of your pivot table.

    If you want to use GETPIVOTDATA, simply type an equal sign, then navigate to your pivot table and click the cell with the data that you want. You will get a formula that looks like this:

    =GETPIVOTDATA("Field 5",PTable!$A$3,"Field 1","Value 1","Field 3","Value 3","Field 4","Value 4")

    The length depends on the number of fields that you've used. If you want, any of the Values can be replaced with cell references that contain valid values, so that the formula can be copied down or across to pull multiple values for a sub table.


    HTH, Bernie

    Thursday, March 08, 2012 5:17 PM