HI All
Trying to build a loss gain report for my company based on exchange rates at invoice issue and receipt of funds, however, as some payments contain multiple invoices I have hit a stumbling block;
Sheet3
<tbody>
</tbody>
So what I need to do in difference (column M) is minus column L from column F but I need the top four results with the same Receipt No. (Column A) so L2 L3 L4 and L5 to all be taken away from F2.
The above is only a small snippet of the overall table so want to make it as easy as possible.
Excel tables to the web >> Excel Jeanie HTML 4
Trying to build a loss gain report for my company based on exchange rates at invoice issue and receipt of funds, however, as some payments contain multiple invoices I have hit a stumbling block;
Sheet3
* | A | B | C | D | E | F | G | H | I | J | K | L | M |
1 | Receipt No. | Receipt Date | Number | Received | Exch Rate | USD | Invoice No. | Invoice Date | Number | Amount | Exch Rate | USD | Difference |
2 | RC149000014 | 09/01/2014 | 41648 | € * * * * * * * 20,649.24 | 1.3590100 | $ * * * * *28,062.52 | SI136000568 | 27/11/2013 | 41605 | € * * 1,707.59 | 1.3564400 | $ * * * * * *2,316.24 | * |
3 | RC149000014 | 09/01/2014 | 41648 | € * * * * * * * 20,649.24 | 1.3590100 | * | SI136000570 | 27/11/2013 | 41605 | € * * 4,438.70 | 1.3564400 | $ * * * * * *6,020.83 | * |
4 | RC149000014 | 09/01/2014 | 41648 | € * * * * * * * 20,649.24 | 1.3590100 | * | SI136000582 | 02/12/2013 | 41610 | € * * 1,789.76 | 1.3550100 | $ * * * * * *2,425.14 | * |
5 | RC149000014 | 09/01/2014 | 41648 | € * * * * * * * 20,649.24 | 1.3590100 | * | SI136000604 | 20/12/2013 | 41628 | € * 12,713.19 | 1.3671500 | $ * * * * *17,380.84 | * |
6 | RC149000012 | 10/01/2014 | 41649 | € * * * * * * * * * * 309.82 | 1.3663500 | $ * * * * * * * *423.32 | SI136000589 | 11/12/2013 | 41619 | € * * * * 309.82 | 1.3793200 | $ * * * * * * * *427.34 | * |
7 | RC149000013 | 13/01/2014 | 41652 | € * * * * * * * * 1,103.13 | 1.3645200 | $ * * * * * *1,505.24 | SI136000600 | 18/12/2013 | 41626 | € * * 1,103.13 | 1.3762800 | $ * * * * * *1,518.22 | * |
<tbody>
</tbody>
So what I need to do in difference (column M) is minus column L from column F but I need the top four results with the same Receipt No. (Column A) so L2 L3 L4 and L5 to all be taken away from F2.
The above is only a small snippet of the overall table so want to make it as easy as possible.
Excel tables to the web >> Excel Jeanie HTML 4