Pivot table with Date and Cell to contain Formula.

[ianb]

Hi.

I have a pivot table that updates each day. It has various dates showing when a task is due e.g 23/03/2015 or 08/04/2015 Also I have a Cell at the top of the Spreadsheet where I would like to enter a formula that will lookup if the date in the pivot table and enter in the cell box. THis is good for Service level agreements to show how may tasks need to be completed when the pivot table is updated.

Dates are in a range B152 to B176 yet can be more up to B190 as an example.

Data is shown C152 to C176

Can anyone advise on this formula please.
I would think it involves Lookup yet my array is not rght at present. keeps returning the last title not locating today.

=LOOKUP("=today ()",B152:B188)

 

[Khalid NGO]

Hi inab,
Please include your sheet with sample data, this will help members to give you targeted solution.

Regards ,

 

[ianb]

Here it is ?

 

[Asheesh]

Hi ianb - can you explain a little more...
You say "Dates are in a range B152 to B176 yet can be more up to B190 as an example" but if we go through the data tab, dates are keyed in upto C265.. I dont see dates anywhere in Column B.

Appreciate if you could share a couple of examples and required result..

 

[ianb]

In htr epivot table example A5 to A28 this could become more dates A5 to A31 also it could be come less. Column B would have the totals.

I want to be able to state =Today()-2 will be 06/04 as 08/04 is today. and then will count the numbers for the the date and previous and place the total number in a cell.

Sp With the exampel the retreun would be for the 06/04 = 0 or 06/04 and previous would be all the dates 09/02 to 04/04 added up in a cell.

 

[Khalid NGO]

Your formula giving #REF error.
I am still in the dark.

 

[ianb]

Final update. Have found AN ANSWER !!! How do I incorporate all the dates Today -2 and all previous else my formula will become TOO LONG !!

 

[ianb]

Hi I found something like this works for me. will have to change the forumula to work with this spreadsheet. I have another one with different pont of referenaces..... it works going back 5 days with today being -2 days. =IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-1+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-2+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-3+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-4+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-5+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-6+TIME(23,59,0)),"0") +IFERROR(GETPIVOTDATA("Incident ID+",$B$257,"Group*+",D4,"Approved",$G$3-7+TIME(23,59,0)),"0")