If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Index and Match results vary in Excel 2003 and 2007
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 |
Thread Tools | |
Display Modes | |
|
|