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

thoaingan

New Member
Joined
Oct 9, 2014
Messages
4
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!




 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

DeBeuz

Active Member
Joined
Jun 6, 2012
Messages
448
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).
 
Last edited:

thoaingan

New Member
Joined
Oct 9, 2014
Messages
4
Last edited:

thoaingan

New Member
Joined
Oct 9, 2014
Messages
4
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

Active Member
Joined
Jun 6, 2012
Messages
448
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.


 

Watch MrExcel Video

Forum statistics

Threads
1,108,989
Messages
5,526,095
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top