Hello all,
I have used Openoffice Calc to make some reports. Now the company gave me excel to work with which i was excited about.
The problem is that some of my OO (OpenOffice) formulas don't do the trick anymore, and i'm not that great in formula debugging so thats why i turn to your expertise
The following issue occurs:
<tbody>
</tbody>
I extract data with two columns, Subject (A column) and Count (B column). I filter the subject and use: =OR(A3="Telephone in"; A3="Chat"; A3="Internet"; A3="E-Mail"; A3="Letter in") in D3. This gives met the TRUE or FALSE values. In E2 i used: =E2+MATCH(1;OFFSET($D$3;E2;0;$C$3-E2;1);0)where F2 is zero value. This returned the position of each TRUE value in the D column. After this i just need to copy the INDEX from the corresponding TRUE values into G column using: =IF(ISERROR(E3);"";INDEX(A$3:A$14;E3)).
Sothe problem here is the =E2+MATCH(1;OFFSET($E$3;E2;0;$D$3-E2;1);0) formula. Does anyone know what goes wrong here??
The I to O columns is what shows when working.
Hope someone can help me out here.
Regards,
Evil
I have used Openoffice Calc to make some reports. Now the company gave me excel to work with which i was excited about.
The problem is that some of my OO (OpenOffice) formulas don't do the trick anymore, and i'm not that great in formula debugging so thats why i turn to your expertise
The following issue occurs:
| 1 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
| 2 | Total contacts | 0 | Filtered | Total contacts | 0 | Filtered | |||||||||
| 3 | Telephone in | 4.545 | 12 | TRUE | #N/A | Telephone in | 4.545 | 12 | TRUE | 1 | Telephone in | 4545 | |||
| 4 | Work Order | 2.128 | FALSE | Work Order | 2.128 | FALSE | 3 | Internet | 1259 | ||||||
| 5 | Internet | 1.259 | TRUE | Internet | 1.259 | TRUE | 4 | Chat | 721 | ||||||
| 6 | Chat | 721 | TRUE | Chat | 721 | TRUE | 5 | 532 | |||||||
| 7 | 532 | TRUE | 532 | TRUE | 9 | Letter in | 2 | ||||||||
| 8 | E-mail out | 238 | FALSE | E-mail out | 238 | FALSE | #N/A | ||||||||
| 9 | Letter out | 71 | FALSE | Letter out | 71 | FALSE | #N/A | ||||||||
| 10 | Telephone out | 26 | FALSE | Telephone out | 26 | FALSE | #N/A | ||||||||
| 11 | Letter in | 2 | TRUE | Letter in | 2 | TRUE | #N/A | ||||||||
| 12 | 0 | 0 | FALSE | 0 | 0 | FALSE | #N/A | ||||||||
| 13 | 0 | 0 | FALSE | 0 | 0 | FALSE | #N/A | ||||||||
| 14 | 0 | 0 | FALSE | 0 | 0 | FALSE | #N/A | ||||||||
| 15 |
<tbody>
</tbody>
I extract data with two columns, Subject (A column) and Count (B column). I filter the subject and use: =OR(A3="Telephone in"; A3="Chat"; A3="Internet"; A3="E-Mail"; A3="Letter in") in D3. This gives met the TRUE or FALSE values. In E2 i used: =E2+MATCH(1;OFFSET($D$3;E2;0;$C$3-E2;1);0)where F2 is zero value. This returned the position of each TRUE value in the D column. After this i just need to copy the INDEX from the corresponding TRUE values into G column using: =IF(ISERROR(E3);"";INDEX(A$3:A$14;E3)).
Sothe problem here is the =E2+MATCH(1;OFFSET($E$3;E2;0;$D$3-E2;1);0) formula. Does anyone know what goes wrong here??
The I to O columns is what shows when working.
Hope someone can help me out here.
Regards,
Evil
