Excel Sample.xls - SAP HR and Payroll Solutions - Strategic Data by wanghonghx

VIEWS: 226 PAGES: 16

									1   10         11                             11
2   20         22                             22
3   30         33                             33
4   40         44                             44
5   50         55                             55
A   B         A+B                   A+B
                           Using "paste special"
         The above cells   you can copy and
         contain the       paste the formula as
         formula           values
1
1



    Clicking on the button above - runs the macro called "MacroTest"
the macro called "MacroTest"
Number      Fruit       Pips         Colour
          6 apple       yes          red
          5 apple       yes          red

The above Auto Filter finds all the apples
that are red in colour. Note that where a filter
criteria has been specified, the arrow
changes colour from black to blue. Note that
Numbers                             Fruit
                                1
                                    apple

Numbers                             Fruit
                                1   pear
                                3   apple
                                2   pear
                                3   apple
                                2   apple
                                3   pear

The result will select rows which
have either a 1 or an apple and
can be seen below

Numbers                             Fruit
                                1   pear
                                3   apple
                                1   apple
                                2   apple
Choose the selection on the left as the Criteria




Choose the selection on the left as the List Range




If you wanted to see which rows had both a 1 and apple in them then you would
need to change the Criteria, such that 1 and apple are adjacent to one another -
in the same row.
Colour       Number
Yellow              2
Yellow              4
Yellow Total        6
Green             10
Green             20
Green Total       30
Grand Total       36
Car      Colour
Metro
Fiesta            Try and enter data in cell D1
Mercedes
BMW
Proton



Red
Green
Yellow
Count of Pip      Stem Pip
                  No                No Total Yes Yes Total Grand Total
Fruit      Colour No   Yes                   Yes
Apple      Green                               1         1           1
Apple Total                                    1         1           1
Banana      Yellow      1                  1                         1
Banana Total            1                  1                         1
Orange      Orange                1        1                         1
Orange Total                      1        1                         1
Pear        Green                              1         1           1
Pear Total                                     1         1           1
Grand Total             1         1        2   2         2           4


Fruit      Colour    Stem   Pip
Apple      Green     Yes    Yes
Banana     Yellow    No     No
Orange     Orange    No     Yes
Pear       Green     Yes    Yes
1000    100   1000
1025    500   1025
       1000
       1010
       1011
       1012
       1013
       1014
       1015
       1025
1   a   1a
2   b   2b
3   c   3c
4   e   4e
5   r   5r
6   g   6g
11/20/2001   Tuesday     3
11/21/2001   Wednesday   4
11/22/2001   Thursday    5
11/23/2001   Friday      6
11/24/2001   Saturday    7
11/25/2001   Sunday      1
            Apples      Pears   Oranges   Bananas   Onion    Potato
Colour      Green/Red   Green   Oranges   Yellow    Yellow   Brown
Pips        Yes         Yes     Yes       No        No       No
Stem        Yes         Yes     No        No        No       No
Fruit       Yes         Yes     Yes       Yes       No       No
Vegetable   No          No      No        No        Yes      Yes
Apples    Pears    Bananas
      510       22       33
Apples    Pears       Bananas
      500         2          3
Apples        Pears        Bananas
         10           20         30

								
To top