GETPIVOTDATA Help!

Bear with me as this is my first time posting!

Referencing the screenshot attached, I am attempting to pull data from the Pivot table, matching Resource types and populating the Week1, Week2, Week3 columns with a %. (% utilization per week for each resource type). I have several other sheets and several Named Ranges, Tables etc.

I am populating the StaffResource column in the Resource Type Dashboard with a named range (Resource2) on the Legend tab.

The other table (L2:O12) is a pivot table that combines all hours for each Resource type entered in a separate table(Project Assignment - not shown). The PivotTable works great. As additional resources and hours are added to the Project Assignment table, the Pivot table updates.

However, when I try to pull the data for each row of the PivotTable as it matches the values in the Resource Type Dashboard columns, i always end up with just the ".NetDeveloper" hours.

=GETPIVOTDATA("Sum1",$L$2,"Resource Type",".Net Developer") How can I make this work for each resource type in the Resource Type Dashboard table?

Thank you!!! many hours spent trying to figure it out on my own cuz I'm too darn stubborn!

resources.jpg

Hi,

Welcome to the Forum.

Modify the formula as following.

=GETPIVOTDATA("Sum1",$L$2,"Resource Type",G4)

Thanks! and uh...DUH! That worked great. How do I include an IFERROR clause so that fields without data are left blank rather than showing #REF ?

P.S. Probably the FASTEST response I've ever received on a help forum! THANK YOU

You are welcome

=IFERROR(GETPIVOTDATA("Sum1",$L$2,"Resource Type",G4) ,"")

Can I ask a follow-on question or should I start a new thread? The next thing I am struggling with is to write a formula to provide a count of each text entry from another table to perform calculations with. Again, i can get it to work for the first row, but I think because I am using Named Ranges or something it copies the same exact formula for every row of the table. I am starting to think Named Ranges are not as useful as I had once thought! I created Named Ranges for each column of resources in the referenced table. But how do I force it to use a new column for each row in the Resource Type/Total WK Hours table?
table2.jpg

Hi,

Please start a new thread.