Create List from Criteria

    Hi
    I am trying to compile a list from a spreadsheet using one specific criteria but taking information from other columns on that sheet on rows where the criteria is met. Below is an example which will hopefully explain better what I am trying to do:
    Example
    A B C D E
    1 Jones Red Day Car Good
    2 Davies Green Night Car Excellent
    3 Evans Blue Day Bus Good


    I want to create a list of all those rows where "Good" is in column E. But I only want to include the data from columns A,C and D in the list for the rows that meet the criteria.
    I have probably made this as clear as mud, but I really would appreciate any assistance in this.

    Re: Create List from Criteria


    OK highlight your data. Then from the data menu select Filter -> Autofilter.


    This will apply an autofilter to your data. You will notice drop down arrows above each field heading. Click on the arrow which is in the top of the column which contains Good, Excellant etc.. and select Good from the drop down list.


    This will then only show records with Good in the column. You can then copy and paste this data else where to do what you want to it.


    Have a play around, also look at the custom option in the drop down list, give s you a few more options.


    Anyway feedback with any questions.

    Re: Create List from Criteria


    Quote from Badger101

    MIssed your reply before a started typing.


    Why isn't it a good idea?


    Because I am not the only one to use this spreadsheet, there are about 40 other people who will need access to update the sheet, and many of them still think a PC is a man in uniform...

    Re: Create List from Criteria


    Now you've lost me as this does not relate to your original request.


    So are you saying all 40 users want to do the same as you. I.e look at good lines only and then do somehting with the data? So this is just not a one off task?


    Autofiltering is the way to go regardless of what you think. If you are saying your users are so poor they can not use an autofilter, then I think you are intimating that your what to code all the task and link to some button to make it full prove for them. Yes or No?


    If yes what is the full taks you are trying to complete? The code will just control the autofiltering and data manipulation.


    If no then what?

    Re: Create List from Criteria


    Data will be input onto one sheet (worksheet1) with all the details required in about 25 columns.
    On one of those specific columns (which in this case is to do with attendance) if the word "Good" is entered then a list, on a separate worksheet (worksheet2), needs to be updated with the data from 5 of the columns for this occurrence.


    I bet this is no clearer.

    Re: Create List from Criteria


    So why can't you use AutoFilter and then copy the filtered data where needed?


    You could make it really simple for the users by saving the Filtered table to View>Custom Views.

    Re: Create List from Criteria


    I was trying to make the example easier to explain, but in fact the list on worksheet2 should be updated when criteria from 2 columns are met on worksheet1.
    Also, some of the cells will be blank and I have tried autofilter and I just get random numbers appearing in cells that should be blank.


    I'm not helping much am I.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login