none
filter if cell containd value RRS feed

  • Question

  • I want to filter automatically if frame type heading contains only "SP&S", "SSL" ,"Single S", "P&S", "SSXL".

    using advanced filter or Macro or VBA.

    Frame Item code Net Req Frame Type Unit Cost Investment qty
    Root Frame 039273 0 SP&S 4650  
      030323 0   2800  
      050247 0   1800  
      051489 0   4650  
      020640 0 P&S 1800  
      382727 0   1000  
      020362 0   10000  
      031393 0   1000  
      048891 0   1250  
      711927 0   1000  
      036383 0   1000  
      037580 0   10000  
      095407 0   1000  
      031183 0   1000  
    Tip Frame 039274 0 SP&S 4650  
      030325 0   2800  
      049463 0   1800  
      020641 0 P&S 1800  
      628427 0   1000  

    Tuesday, January 19, 2016 6:14 AM

Answers

    1. In another part of the sheet (or even on another sheet), enter Frame Type.
    2. Enter the values SP&S, SSL, Single S, P&S and SSXL in the cells below it (one value per cell).
    3. Select the data table (or its top left cell).
    4. On the Data tab of the ribbon, click Advanced. Excel should highlight the data table as the List Range.
    5. Click in the Criteria Range box, then select the range with Frame Type and the 5 cells below it that you created in steps 1 and 2.
    6. Click OK.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 19, 2016 7:10 AM

All replies

    1. In another part of the sheet (or even on another sheet), enter Frame Type.
    2. Enter the values SP&S, SSL, Single S, P&S and SSXL in the cells below it (one value per cell).
    3. Select the data table (or its top left cell).
    4. On the Data tab of the ribbon, click Advanced. Excel should highlight the data table as the List Range.
    5. Click in the Criteria Range box, then select the range with Frame Type and the 5 cells below it that you created in steps 1 and 2.
    6. Click OK.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 19, 2016 7:10 AM
  • Thank you ..it working well
    Tuesday, January 19, 2016 9:35 AM
  • Hi ,

    Required: in workbook1 ,in coloumn “s” under “investment Qty” header I need qty which is in workbook2 under different sheet.

    Constraint: required qty, if in work book 1 – under Plant filter (column I) if I select country (Ex: PL-GOL) & month As example :Jun-16 & frame type in column Q  as example P&S

    Then ,it search PL-GOL sheet in workbook 2 & in jun-16 row & match P&S ,if it match then returns the value of purchase row in jul-16 coloumn.

    Workbook-1

    D

    E

    H

    I

    J

    Q

    R

    S

    T

    U

     

     

     

    Plant

    PL-GOL

     

     

     

     

     

     

     

     

    Months

    Jun-16

     

     

     

     

     

     

     

     

    Frame

    Item code

    Net   Req

    Frame   Type

    Unit   Cost

    Investment   qty

    Investment   value

    Cust   Return Frames-Qty

    SP&S

    039273

    Root   Frame

    039273

    26

    SP&S

    5000

     

     

     

    P&S

    020640

     

    020640

    47

    P&S

    2550

     

     

     

    SP&S

    039274

    Tip   Frame

    039274

    26

    SP&S

    5000

     

     

     

    P&S

    020641

     

    020641

    47

    P&S

    2550

     

     

     

    P&S

    033352

    Extender

    033352

    26

    P&S

    0

     

     

     

    P&S

    184337

     

    184337

    21

    P&S

    0

     

     

     

    SP&S

    041979

    Support

    041979

    26

    SP&S

    0

     

     

     

    P&S

    184837

     

    184837

    26

    P&S

    0

     

     

     

    Workbook-2 will send in next mail

    Tuesday, January 19, 2016 10:24 AM
  • Hi,

    Workbook-2

    c

    D

    E

    F

    G

    H

    I

    J

    K

    L

    M

    N

    Cradle Calculation

     

     

    Type

    Extra

    Jan

    Feb

    Mar

    Apr

    May

    Jun

    Jul

    #N/A

    Total Demand

     

     P&S

    #N/A

       42

       44

       42

       56

       23

       47

       32

    Opening stock

     

       186

     

     

     

     

     

     

     

     

     

    Minimum stock

     

            5

     

     

     

     

     

     

     

     

     

    + Return from customer

     

     

     

     

     

     

    51

    48

    30

       42

       44

    +/- Transfer between plants

     

     

     

     

     

     

     

     

     

     

     

    + Purchase

     

     

     

     

       14

         -  

       65

         -  

         -  

         -    

       56

    Available at month end

     

     

     

     

     144

     100

     109

     101

     108

     103

     115

    Tuesday, January 19, 2016 10:25 AM
  • Hi marutiis,

    Based on your reply, do you have additional question? It seems that the additional question isn’t directly related to the original issue, I’d suggest that you could asking separate questions in new forum threads. Thus, forum readers may recognize questions and answers easily.

    For your original issue, you said that “it working well”, you could mark the Hans’s reply to close this thread.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 20, 2016 8:05 AM
    Moderator