K
Krish
I have 2 Pivot Tables in a worksheet. They both have Date, Total amount. One Pivot table is producing deposit info from the ERP data while the other one is extracted from Bank ACH payments. Since the transactions are not occurring on the same date, I am trying to match the deposit amount.
My formula is =IF(COUNTIF(F24,"*Total")," ",INDEX(GLCC,MATCH(H24,GLCC,0),0)). GLCC is a named range. Since Pivot table produce Totals by categories, I use COUNTIF. This formula if refreshed in Excel 2003, I get the best results. But if I refresh in Excel 2007 (compatability mode), it shows #N/A. I had verified the correct range in Name in Excel 2007 and ther refer to the correct range.
Could I get any advise from the experts to get the correct results?
DEP_DATE Total Date Total
10/5/2009 4,525.68 4,525.68 10/1/2009 4,418.83 #N/A
10/6/2009 3,583.50 3,583.50 10/8/2009 3,653.69 3653.69
10/7/2009 3,024.79 3,024.79 10/15/2009 6,030.40 6030.4
10/8/2009 6,370.78 6,370.78 10/22/2009 3,968.91 3968.91
10/9/2009 4,991.50 4,991.50 18,071.83
10/12/2009 4,394.37 4,394.37 10/5/2009 5,482.06 #N/A
10/13/2009 2,980.85 #N/A 10/6/2009 4,920.74 #N/A
10/14/2009 1,761.67 1,761.67 10/7/2009 923.14 923.14
10/15/2009 4,574.42 4,574.42 10/9/2009 827.61 827.61
10/16/2009 5,242.27 5,242.27 10/12/2009 515.43 515.43
10/19/2009 4,777.96 4,777.96 10/13/2009 299.22 299.22
10/20/2009 4,318.45 4,318.45 10/14/2009 4,024.47 4024.47
10/21/2009 4,540.09 4,540.09 10/15/2009 582.32 582.32
10/22/2009 3,310.31 #N/A 10/16/2009 3,895.15 #N/A
10/23/2009 1,093.55 #N/A 10/19/2009 208.78 208.78
10/23/2009 6,169.83 #N/A 10/20/2009 2,318.89 2318.89
10/26/2009 7,428.85 #N/A 10/22/2009 467.85 467.85
10/27/2009 3,476.37 #N/A 10/23/2009 1,472.90 #N/A
76,565.24 25,938.56
10/5/2009 3,277.15 3,277.15 10/1/2009 12,563.26 #N/A
10/5/2009 4,094.78 4,094.78 10/2/2009 3,704.96 #N/A
10/7/2009 22,376.79 22,376.79 10/5/2009 5,052.18 #N/A
10/9/2009 3,653.69 3,653.69 10/6/2009 4,525.68 4525.68
10/9/2009 827.61 827.61 10/7/2009 3,583.50 3583.5
10/13/2009 515.43 515.43 10/8/2009 3,024.79 3024.79
10/13/2009 3,652.77 3,652.77 10/9/2009 6,370.78 6370.78
10/14/2009 4,024.47 4,024.47 10/10/2009 499.51 499.51
10/14/2009 299.22 299.22 10/11/2009 427.73 427.73
10/14/2009 582.32 582.32 10/12/2009 4,991.50 4991.5
10/15/2009 6,030.40 6,030.40 10/13/2009 4,394.37 4394.37
10/15/2009 331.78 331.78 10/14/2009 2,980.85 #N/A
My formula is =IF(COUNTIF(F24,"*Total")," ",INDEX(GLCC,MATCH(H24,GLCC,0),0)). GLCC is a named range. Since Pivot table produce Totals by categories, I use COUNTIF. This formula if refreshed in Excel 2003, I get the best results. But if I refresh in Excel 2007 (compatability mode), it shows #N/A. I had verified the correct range in Name in Excel 2007 and ther refer to the correct range.
Could I get any advise from the experts to get the correct results?
DEP_DATE Total Date Total
10/5/2009 4,525.68 4,525.68 10/1/2009 4,418.83 #N/A
10/6/2009 3,583.50 3,583.50 10/8/2009 3,653.69 3653.69
10/7/2009 3,024.79 3,024.79 10/15/2009 6,030.40 6030.4
10/8/2009 6,370.78 6,370.78 10/22/2009 3,968.91 3968.91
10/9/2009 4,991.50 4,991.50 18,071.83
10/12/2009 4,394.37 4,394.37 10/5/2009 5,482.06 #N/A
10/13/2009 2,980.85 #N/A 10/6/2009 4,920.74 #N/A
10/14/2009 1,761.67 1,761.67 10/7/2009 923.14 923.14
10/15/2009 4,574.42 4,574.42 10/9/2009 827.61 827.61
10/16/2009 5,242.27 5,242.27 10/12/2009 515.43 515.43
10/19/2009 4,777.96 4,777.96 10/13/2009 299.22 299.22
10/20/2009 4,318.45 4,318.45 10/14/2009 4,024.47 4024.47
10/21/2009 4,540.09 4,540.09 10/15/2009 582.32 582.32
10/22/2009 3,310.31 #N/A 10/16/2009 3,895.15 #N/A
10/23/2009 1,093.55 #N/A 10/19/2009 208.78 208.78
10/23/2009 6,169.83 #N/A 10/20/2009 2,318.89 2318.89
10/26/2009 7,428.85 #N/A 10/22/2009 467.85 467.85
10/27/2009 3,476.37 #N/A 10/23/2009 1,472.90 #N/A
76,565.24 25,938.56
10/5/2009 3,277.15 3,277.15 10/1/2009 12,563.26 #N/A
10/5/2009 4,094.78 4,094.78 10/2/2009 3,704.96 #N/A
10/7/2009 22,376.79 22,376.79 10/5/2009 5,052.18 #N/A
10/9/2009 3,653.69 3,653.69 10/6/2009 4,525.68 4525.68
10/9/2009 827.61 827.61 10/7/2009 3,583.50 3583.5
10/13/2009 515.43 515.43 10/8/2009 3,024.79 3024.79
10/13/2009 3,652.77 3,652.77 10/9/2009 6,370.78 6370.78
10/14/2009 4,024.47 4,024.47 10/10/2009 499.51 499.51
10/14/2009 299.22 299.22 10/11/2009 427.73 427.73
10/14/2009 582.32 582.32 10/12/2009 4,991.50 4991.5
10/15/2009 6,030.40 6,030.40 10/13/2009 4,394.37 4394.37
10/15/2009 331.78 331.78 10/14/2009 2,980.85 #N/A