GetPivotData for the past 5 weeks

[skoorBmaS]

I am currently using a pivot table which shows information regarding how much time an employee has worked, which is separated by week number. I would ideally like a GETPIVOTDATA formula to take this information and place it onto a dashboard screen, however only show the data from the past 5 weeks.

To get a week number, I am using a cell which has =TODAY() and then another one which uses =WEEKNUM() to provide a week number for that day. However in the GETPIVOTDATA formula, I can only get it to get data for a certain week (e.g. 21, rather than 21, 20, 19, 18 and 17). By using this method, I hope that the pivot data which is being extracted will automatically update based on today's date.

Does anyone know how to do this? The current formula which I am using is as below

=(GETPIVOTDATA("Hrs+Travel",Pivots!$AA$2,"Week",21,"Name","Bill"))

 

[RoryA]

Do you want 5 separate results, or one total for the last 5 weeks?

 

[skoorBmaS]

I would like a result for each week, so the table would look like the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Week 17[/TD]
[TD]Week 18[/TD]
[TD]Week 19[/TD]
[TD]Week 20[/TD]
[TD]Week 21[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So then when the next week comes, the values at the top of the table shall automatically update to show this week as the value on the far right.

 

[RoryA]

Then I'd suggest that you just enter numbers into those header cells and give them a custom number format of "Week "0 so that they display as shown above. You can then just refer to them in the GETPIVOTDATA formula - for example:

=GETPIVOTDATA("Hrs+Travel",Pivots!$AA$2,"Week",B$1,"Name",$A2))

where B1 has the week number and A2 has the name.

 

[skoorBmaS]

Hi Rory,

Thanks for the suggestion, works perfectly as intended