Thread: pivot table linked report using function

HTML Code: 

I AM USING PIVOT TABLE IN MY PRODUCTION PLANNING. I GET PIVOT REPORT AS BELOW

date	       ord-1	ord-2	ord-3
1-Dec-10	 	10	 
2-Dec-10	 	34	 
3-Dec-10	 	28	 
4-Dec-10	 	32	 
5-Dec-10	30	 	 
6-Dec-10	30	 	 
7-Dec-10	30	 	 
8-Dec-10	 	 	30
9-Dec-10	 	 	30
10-Dec-10	 	 	30
11-Dec-10	 	 	 
12-Dec-10	 	 	 
13-Dec-10	 	 	 
14-Dec-10	 	 	 


FROM THE ABOVE TABLE I WOULD LIKE TO MAKE A REPORT AS PER BELOW



ORD #           START DATE           END DATE        SUM OF QTY
ORD-1            05-DEC-2010           07-DEC-2010       90
ORD-2            01-DEC-2010           04-DEC-2010       104
ORD-3           08-DEC-2010            10-DEC-2010       90 [EMAIL="murarihyd@gmail.com"]murarihyd@gmail.com[/EMAIL]

Not entirely sure I understand but in general terms - based on the specific sample:

B22:
=MIN(IF(ISNUMBER(INDEX($B$4:$D$17,0,ROWS(B$22:B22))),$A$4:$A$17))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)

C22:
=LOOKUP(9.99E+307,INDEX($B$4:$D$17,0,ROWS(C$22:C22)),$A$4:$A$17)
confirmed with Enter

D22:
=SUM(INDEX($B$4:$BD$17,0,ROWS(D$22:D22)))
confirmed with Enter

B22:D22 copied down as nec.

Originally Posted by murarihyd

can you fill it up the attachement and post it.

Re: B22 - I suspect you did not confirm as an Array (confirming the formula with Enter alone will not suffice as stated previously)
You need to be able to do this on your own so in this instance I think you will get some value recreating it yourself.

Re: C22
9.99E+307 is a big number: 9.99*10^307
This number used with LOOKUP in this context will return the last numeric value found in the precedent range (lookup_vector)
Where a result_vector is specified (as is the case here) the value associated with the last numeric value found in the lookup_vector is returned - here for ex. we return the associated date.

For more info. on last values etc see: http://www.xldynamic.com/source/xld.LastValue.html