+ Reply to Thread
Results 1 to 6 of 6

If statement that outputs to next available row on another sheet.

  1. #1
    Registered User
    Join Date
    07-30-2008
    Location
    Georgia, USA
    Posts
    9

    If statement that outputs to next available row on another sheet.

    Hi all. I am a first time poster and I am sure it will show. I am working on a simple contact list for communication distribution. The first sheet lists all known company sales contacts (customers and prospects).

    We have 2 basic customer types (lets call them type 1, and type 2). On the same first sheet I have two columns for each of these distinctions and the data entry is "yes" or "no". My objective is to copy this contact information to a different sheet depending on the data in these two columns. If "type 1" equals yes, then copy entire row to the sheet titled Type 1 customers. If "type 2" equals yes, then copy entire row to the sheet titled Type 2 customers. If the answer is "no" to both types of customers, then copy the entire row to the sheet named Prospects.

    I realize that these would be fairly simple IF statements but when copying the data to the other sheets I would like it to find and fill the next available row so that the lists are not broken up by a lot of empty rows. Any suggestions?

    Thanks for helping a newbie!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi and welcome to the forum.

    I'd be inclined to consider whether you really need to do this when you can use Data Filter AutoFilter, and use the drop down arrows that appear on the first row of the database column labels. You can selectively filter the data by clicking and selecting form these drop downs.

    If you really do need to copy subsets of the data to three separate sheets, then use Data Filter Advance Filter, and use the Type and Yes/No in a criteria range. If you do this then start the Data Filter.... selection process from the sheet to which you want to filter to, otherwise Excel will complain.

    Ultimately it would make sense to create a macro to do this automatically, but I suggest you play around with doing it manually first so you can see how it works.

    HTH

  3. #3
    Registered User
    Join Date
    07-30-2008
    Location
    Georgia, USA
    Posts
    9
    Richard,

    Thanks for the reply. I am not familiar with that feature so I will check it out. I am assuming that I would set this up by having a drop down in the "Customer Type" (type 1, type 2, prospect) and not use my 2 column solution. I would then use the filter to show only each type of customer. That could suffice.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Not quite.

    Assuming your database starts in row 10, with row 10 being the column labels, then with the cursor anywhere in the database choose the Data Autofilter from the menu. You'll see drop down arrows appearing in all the cells on the top row.

    Now you can use your two columns to filter on both the customer type column, and the yes/no column - or indeed any other columns.

    Whilst you're playing around with the database, check out the Pivot Table functionality as well. This will sum and count your data in seconds, putting the results in a separate table. It may not be any use to you with this data, but bear it in mind for other data sets.

    Rgds

  5. #5
    Registered User
    Join Date
    07-30-2008
    Location
    Georgia, USA
    Posts
    9
    Nice feature. Thanks.

    Is it possible to create a permanent drop-down or three buttons at the top of the worksheet that would filter when selected or pressed (to show prospects, customer type 1, customer type 2)? Obviously each of these selections would be tied to a filtering macro.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,
    Yes of course. Just create or record the appropriate code for each of the filtering options, and then attach each button to its own code.

    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1