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. 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 ranges in Excel 2007 and they refer to the correct data range.
Could I get any advise from the experts what is wrong wth the formula in Excel 2007 (Compatability mode)?
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. 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 ranges in Excel 2007 and they refer to the correct data range.
Could I get any advise from the experts what is wrong wth the formula in Excel 2007 (Compatability mode)?
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