How do I do a vlookup or index and match with a changing pivot table
I have a pivot table with weekending dates going across the top, and names going down column A. The data of course is in the center. I am trying to pull only the last 8 weeks of data. So I created a sheet that gives me the last 8 weeks of dates on top, and I have the names going down column A. I did this by using my weekending formula across the top then -7 - 14 -21 etc up to 8 weeks. This formula points to a cell with the formula =Today() in it and based on that date, my date cells will have the last 8 weeks in it. Now all I want to do is pull the last 8 weeks of data from my pivot. Which would be best to use INDEX and Match or a Vlookup? I want to lookup the data in my pivot and have it displayed in my table tab.
samplesheet61015.xlsx
samplesheet61015.xlsx
ASKER
my question here is when the Pivot updates with next week data will the data change on the table sheet automatically?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That not what I want. My dates will change on the header row automatically next week. So the week ending date on the table sheet will be 6/19/15 and the rest of the dates will change as well . I want the data on the table sheet, that does the look up, to change with the dates. If you still have a copy of the sheet just go to O1 and change that to 6/15/15 and you will see all the dates change on the table sheet. After they change I need the updated data for weekending 6/19/15 automatically.
The formulas as posted (all of them) will update automatically as required.
I tested that assertion by changing the formula in O1 to:
=TODAY()-7
The dates (and values in the first two tables changed as desired.
The header labels and values in the third table did not change because they are always returning the eight most recent weeks data. In other words, if there were more data in the PivotTable, the third table would be returning it. If there were no incidents in a week, the PivotTable would omit that week from its summary, and the third table would return the most recent eight weeks that had data reported. The first two formulas, which rely on fixed dates, would return #N/A for the week with no incidents.
I tested that assertion by changing the formula in O1 to:
=TODAY()-7
The dates (and values in the first two tables changed as desired.
The header labels and values in the third table did not change because they are always returning the eight most recent weeks data. In other words, if there were more data in the PivotTable, the third table would be returning it. If there were no incidents in a week, the PivotTable would omit that week from its summary, and the third table would return the most recent eight weeks that had data reported. The first two formulas, which rely on fixed dates, would return #N/A for the week with no incidents.
ASKER
Ok I figured out the issue with the Table sheet, I did not use the named range when making my pivot so it did not update the pivot at all. Once I fixed that it worked flawlessly.
ASKER
Thanks for all the help!
Alternatively:
On sheet1 add this formula to column K with header "Valid":
=IF(J2<TODAY()-(8*7),"","V
Amend the pivot to include this data column and when refreshed drag the Valid field as a Report Filter. In the Pivot you with then get the Valid header and dropdown in B2 on Pivot sheet. Use the dropdown to select "Valid" and only 8 weeks of data will show in the pivot.
Thanks
Rob H
On sheet1 add this formula to column K with header "Valid":
=IF(J2<TODAY()-(8*7),"","V
Amend the pivot to include this data column and when refreshed drag the Valid field as a Report Filter. In the Pivot you with then get the Valid header and dropdown in B2 on Pivot sheet. Use the dropdown to select "Valid" and only 8 weeks of data will show in the pivot.
Thanks
Rob H
ASKER
Wow... that worked great too! Thanks!
ASKER
Rob Henson, I just added new data and now I get 9 weeks in the pivot. I do not know why.
ASKER
The other weeks are hidden but for some reason I am getting 4/17 - 6/12
=GETPIVOTDATA("Incident ID*+",Pivot!$A$3,"Assignee
=VLOOKUP($A4,Pivot!$A$4:$Z
When you build a formula that acts on a PivotTable, you get a GETPIVOTDATA formula if you click on the cells to establish their address. This normally leads to trouble when the PivotTable is refreshed--but I think you'll be OK with the formula edited as shown. Please do doublecheck what happens when the dates are partly in one year and partly in another, however.
samplesheetQ28688811.xlsx