Excel: Data filtering using formulas > Openoffice formula does not work in excel


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 [IMG]https://lh5.googleusercontent.com/m6zm3T_qaCifw-3rTfxmrKR7MZyKP3nQ7a8Bma0ZliUtYqJ_XqHjoT8_76BR_066oh3gpSrxaUUf4NujW2NjDYplCgIZ7WqfbRWpAwhTKT-w-pYve5dFI58PSeRxcmViTA[/IMG] 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 E-mail 532
7 E-mail 532 TRUE E-mail 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
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


This question generated 20 answers. To proceed to the answers, click here.

This thread is current as of July 26, 2014.


For more resources for Microsoft Excel