Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I am trying to accomplish the following in excel 2007:

    On sheet 1 I have a large product list with the following columns:
    Amount-productnr-productdescription-price

    On sheet 2 I would like to create a list of rows from sheet 1 where the column 1 has a value of 1 or more.

    Is it possible to create such a reference without using a macro?

    I have added an example of what sheet 2 would has to look like, based on the values of sheet 1

    Thanks,

    Gerben
    The Netherlands
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at Advanced Filter

    But it would need a different Layout
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello G - I have attached a revised file. No Macros needed !!! This uses Data/Filter/AutoFilter.

    I put all of your Sheet1 info on a new tab called Data. Could you Layout your info to be similar to the Data tab attached?
    This way you will be able to sort, subtotal, filter, etc on the columns of your choice. You can also make some nice Pivot Tables.

    Here is filtering results for your Amount column -
    On the tab called Data, click the arrow after Amount and select (All) to see all of your info.
    On the tab called Data, click the arrow after Amount and select (Custom ...), Choose "is greater than" and "0" to see the info you wanted.

    You can also do other Filtering by your Group column -
    On the tab called Data, click the arrow after Group and select 1 to see only your Group 1 info.
    On the tab called Data, click the arrow after Group and select 2 to see only your Group 2 info.

    You have many possibilities if you can change slightly the layout of your original Sheet1. You will no longer need Sheet1 or Sheet2.

    No Macros used !!


    Tim

    PS. For those of us with older versions of Excel use the file with the .xls extension, not the .xlsx extension.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •