Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Excel Formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |