vipulgos
Active Member
- Joined
- Aug 17, 2002
- Messages
- 335
- Office Version
- 2010
- Platform
- Windows
Dear all,,
I am using the formula for accounting purpose which extract data from other sheet (company) in the same book,but autofilling the cells below in sheet called "bank", when there is no match in original sheet(company) , it writes #ref! error and so the Autosum below will also gives me error.
I want excel to help me like thiscompany)
If there is error(#ref!), it should automatically write 0 in that column, so my Autosum will sum only the data actually extracted
Excel 2003
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
Company sheet is as under
Excel 2003
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
I am using the formula for accounting purpose which extract data from other sheet (company) in the same book,but autofilling the cells below in sheet called "bank", when there is no match in original sheet(company) , it writes #ref! error and so the Autosum below will also gives me error.
I want excel to help me like thiscompany)
If there is error(#ref!), it should automatically write 0 in that column, so my Autosum will sum only the data actually extracted
Excel 2003
A | B | C | D | E | |
---|---|---|---|---|---|
1 | |||||
2 | CREDIT | DEBIT | |||
3 | Amount | Came From | Amount | Paid for | To |
4 | |||||
5 | 11 | abc | ` 100 | stamp paper | misc. |
6 | 300 | efg | ` 96 | xyz | bank |
7 | 300 | hij | #REF! | ||
8 | 250 | xyz | #REF! | ||
9 | #REF! | #REF! | |||
10 | #REF! | #REF! | |||
11 | #REF! | ||||
12 | #REF! | ||||
13 | |||||
14 | |||||
15 | |||||
16 | #REF! | #REF! |
<tbody>
</tbody>
BANK
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
Array Formulas
<tbody> </tbody> Note: Do not try and enter the {} manually yourself |
<tbody>
</tbody>
Company sheet is as under
Excel 2003
A | B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|---|
7 | 11.00 | abc | company | bank | for misc. expense | misc. | 11.00 | xyz | company | xyz |
8 | 25.00 | def | company | Cash | for misc. expense | mic | 11.00 | abc | company | xyz |
9 | 300.00 | efg | company | bank | for misc. expense | misc. | 120.00 | plan copies | company | xyz |
10 | 300.00 | hij | company | bank | for misc. expense | misc. | 200.00 | plan copies | company | xyz |
11 | 250.00 | mno | company | Cash | for misc. expense | misc. | 100.00 | stamp paper | bank | xyz |
12 | 250.00 | xyz | bank | Cash | for misc. expense | misc. | 400.00 | stamp paper | company | xyz |
13 | bank | 96.00 | xyz | bank | xyz | |||||
14 | misc. | 70.00 | Rubber stamp | company | xyz | |||||
15 | misc. | 40.00 | pan card courier charge | company | xyz | |||||
16 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
company