How to look up data for a cell in a Pivot Table

[thoaingan]

I have some raw data as shown in Image 1. I created a Pivot Table for it, mainly just for formatting purposes (the ability to nicely group and expand the sub-items). The Pivot Table is shown in Image 2.

I need to display the LastUsageTime for each of the items (UsageName) listed in the Pivot Table. It is simply a lookup of the LastUsageTime column from the Raw Data sheet. Therefore, I cannot add this column to the Pivot Table because the PT wants me to use one of the aggregate functions. I can't do a VLOOKUP either since each of those items is not unique by itself (for example, "WIT - Bug" appears multiple times in Raw Data). However, the combination of columns TPC+TeamProjectName+UsageName is unique.

How should I do the lookup to retrieve the LastUsageTime data?

Thanks for any help!

 

[DeBeuz]

The images are missing!

But for now: in a cell you can pick a value from a pivot by typing "=" in the formula box and click the required pivot cell.
This will setup the getpivotdata function for you (in my case =GETPIVOTDATA("Value",Pivots!$A$4,"Per",2,"Cost Ctr",13210,"Cost Elem.",41400002)).
With this you can find all data you need to lookup your date from the raw data.
I assume you know how to lookup using multiple columns (SUMIFS(), create combined columns for VLOOKUP etc).

 

[thoaingan]

My apologies for the missing images. Here they are again. In the mean time, I'll try out your suggestion. Thanks!
I'm also attaching the actual file for reference.
Raw Data: https://drive.google.com/file/d/0BxpAxmlM3lttcTB6UDZlTWg1eTA/view?usp=sharing
Pivot Table: https://drive.google.com/file/d/0BxpAxmlM3lttdDN2WU9FN0tQOG8/view?usp=sharing
Excel File: https://drive.google.com/file/d/0BxpAxmlM3lttODRuWnpIckxsTjQ/view?usp=sharing

 

[thoaingan]

The images are missing!

But for now: in a cell you can pick a value from a pivot by typing "=" in the formula box and click the required pivot cell.
This will setup the getpivotdata function for you (in my case =GETPIVOTDATA("Value",Pivots!$A$4,"Per",2,"Cost Ctr",13210,"Cost Elem.",41400002)).
With this you can find all data you need to lookup your date from the raw data.
I assume you know how to lookup using multiple columns (SUMIFS(), create combined columns for VLOOKUP etc).

Hi Paul,
Your suggestion won't work for me since the cell that I need to do the lookup for (UsageName) is in the ROWS section of the Pivot (not the VALUES section). I hope you can see the images from the Google Drive links I posted earlier. Excel will give me the GETPIVOTDATA if I click the cell in the VALUES section, but not the cells in ROWS, unfortunately.

Thanks,
Angie

 

[DeBeuz]

Hi Angie,

Can you convert the LastUsageTime column to Excel data or add another column with that converted date?
I think you can add the this column using the max function.
If there is only one value, the max won't harm it, otherwise you will get the last date.

 

[thoaingan]

Yes that's what I ended up doing ... using Max(LastUsageTime). Thanks so much!!!