Results 1 to 2 of 2
2014-10-09, 22:30 #1
- Join Date
- Oct 2014
- Thanked 0 Times in 0 Posts
How to look up data for a cell in a Pivot Table
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?
I'm attaching the actual Excel file here as well for reference.
Thanks for any help!
Last edited by Ngan Menegay; 2014-10-09 at 22:33. Reason: Attach Excel file
2014-10-10, 05:45 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
I think the easiest way would be to add an additional column in the raw data that contained =ROW() formula to indicate the row.
Then in Pivot table add a new value field, which is hte mininum of row
Then outside the pivot table, create a formula to index on the lastusagetime column in rawdata with the min row col in the pivot table
You can hide the minrow col in pivot table if desired.