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
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Here are two formulas to consider:
=GETPIVOTDATA("Incident ID*+",Pivot!$A$3,"Assignee+",$A4,"Weekending",B$3-DATE(YEAR(B$3),1,0)+1)
=VLOOKUP($A4,Pivot!$A$4:$Z$12,MATCH(TEXT(B$3,"d-mmm"),Pivot!$A$4:$Z$4,0),FALSE)

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
Edward PamiasTeam Lead RRS DeskAuthor Commented:
my question here is when the Pivot updates with next week data will the data change on the table sheet automatically?
byundtMechanical EngineerCommented:
The data in your table will update if you change the date in cell O1.

I can remove that restriction to updating cell O1 using yet another formula that looks for the Grand total column in row 4, then returns the most recent dates in the eight columns to its left:
=VLOOKUP($A4,Pivot!$A$4:$Z$12,MATCH("Grand*",Pivot!$4:$4,0)-COLUMNS(B$3:$J$3),FALSE)

While the above formula doesn't need a date in cell O1, the column header labels still rely on that date. If you want a formula for the column headers that doesn't rely on a date in O1, you could use:
=INDEX(Pivot!$4:$4,MATCH("Grand*",Pivot!$4:$4,0)-COLUMNS(B$3:$J$3))
The above formula will return the column headers as text, just as they are in the PivotTable.
samplesheetQ28688811.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Edward PamiasTeam Lead RRS DeskAuthor Commented:
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.
byundtMechanical EngineerCommented:
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.
Edward PamiasTeam Lead RRS DeskAuthor Commented:
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.
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thanks for all the help!
Rob HensonFinance AnalystCommented:
Alternatively:

On sheet1 add this formula to column K with header "Valid":

=IF(J2<TODAY()-(8*7),"","Valid")

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
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Wow... that worked great too! Thanks!
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Rob Henson, I just added new data and now I get 9 weeks in the pivot. I do not know why.
Edward PamiasTeam Lead RRS DeskAuthor Commented:
The other weeks are hidden but for some reason I am getting 4/17 - 6/12
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.