+ Reply to Thread
Results 1 to 3 of 3

When I have a certain value in a cell on sheet one, copy entire row to sheet 2?

  1. #1
    Registered User
    Join Date
    04-07-2016
    Location
    Houston, Texas
    MS-Off Ver
    Home and Business 2013
    Posts
    3

    When I have a certain value in a cell on sheet one, copy entire row to sheet 2?

    I am a safety manager for a company with around 100 drivers. Some drivers are class "C" and some have commercial licenses (class "A", "B", "D", etc.). Sheet one has ALL the drivers on it regardless of license class. Sheet 2 has my drivers with commercial licenses on it (their paperwork requires has different requirements). When I add a driver to sheet one, column "F" has whatever the class of license is ("A", "B", "C", "D", etc.).

    Is there a way to have an entire row in which the value of column F is anything but "C" copied to the next available row on sheet 2?

    I'm attaching a sample. Thanks for any help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,109

    Re: When I have a certain value in a cell on sheet one, copy entire row to sheet 2?

    Update every time sheet 2 gets activated

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,151

    Re: When I have a certain value in a cell on sheet one, copy entire row to sheet 2?

    If you are looking for a formula solution, you can put this formula in K3 of Sheet1:

    =IF(OR(F3="",F3="C"),"",MAX(K$2:K2)+1)

    and copy down to beyond your data. Then put this formula in K3 of Sheet2:

    =IFERROR(MATCH(ROWS($1:1),Sheet1!$K:$K,0),"-")

    and this one in A3:

    =IF(OR($K3="-",$K3=""),"",INDEX(Sheet1!A:A,$K3))

    Copy this across to J3, then apply appropriate formatting to D3, E3, G3 and H3 (dates). Then you can copy the formulae in row 3 down as far as you need to. The attached file illustrates this.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ 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