+ Reply to Thread
Results 1 to 7 of 7

Automatically copy rows to another sheet if criteria filled

  1. #1
    Registered User
    Join Date
    12-17-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    5

    Automatically copy rows to another sheet if criteria filled

    Please can someone help me with the following:

    I would like to copy all the rows from the first sheet containing a name i.e. "Andrew", which could be in any of three columns.

    This then needs to be able to work again if I update the first master slide.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,548

    Re: Automatically copy rows to another sheet if criteria filled

    Although you have posted in the Programming forum, you can achieve this using 3 basic formulae (see attached file).

    First of all, I set up a named range called Names in column N of Sheet1, and used this as the source list for the data validation drop-down in cell C1 - this will give you the flexibility to choose any name from the list.

    Then in the clients sheet I put this formula in I3:

    =IF(OR(D3=Sheet1!$C$1,E3=Sheet1!$C$1,F3=Sheet1!$C$1),MAX(I$2:I2)+1,"-")

    which checks to see if the chosen name appears in either column D, E or F, and if it does a unique sequential number for each matching record is set up when it is copied down to beyond the bottom of your data (to allow for new data being added) - the hyphens help to show where the formula is active.

    I've used the same headings in row 3 of Sheet1, but inserted a column A so that I can have this formula in A4:

    =IFERROR(MATCH(ROWS($1:1),ClientAndProspect!I:I,0),"")

    which will find the row on the clients sheet where the first matching record occurs. When copied down, this will look for the 2nd, 3rd, 4th etc. matching record in turn. I've used this formula in B4:

    =IF($A4="","",IF(INDEX(ClientAndProspect!A:A,$A4)="","",INDEX(ClientAndProspect!A:A,$A4)))

    which will bring the data from column A of the clients sheet on the row given by A4. This is then copied across to I4, thus bringing the relevant data from your other columns. I've used the same formatting as in your other table, and so the formulae from A4:I4 can be copied down as far as you think you need them (I've copied to row 40).

    So, all you need to do to use this is to choose a name from the drop-down in C1, and the displayed data will automatically change.

    If you add new data to the master sheet, this will be reflected in Sheet1, as long as the formula in column I is copied beyond your data.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Automatically copy rows to another sheet if criteria filled

    Maybe:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Registered User
    Join Date
    12-17-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    5

    Re: Automatically copy rows to another sheet if criteria filled

    Hi Pete,

    This is so helpful thank you. One final thing, is it easy to edit the formula so that the formatting pulls across?

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,548

    Re: Automatically copy rows to another sheet if criteria filled

    I used the Format Painter to copy the formatting from the main sheet to row 4 of Sheet1. I amended some of the Conditional Formatting rules, and then when the formulae are copied down the formatting goes with them - a formula can't be used to bring formatting from another sheet.

    Hope this helps.

    Pete

    P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  6. #6
    Registered User
    Join Date
    12-17-2015
    Location
    london
    MS-Off Ver
    2010
    Posts
    5

    Re: Automatically copy rows to another sheet if criteria filled

    Thanks Pete,

    I created a few new rules to make sure it always looks smart.

    Thanks for your help - I'll definitely do that!

    Nat

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,548

    Re: Automatically copy rows to another sheet if criteria filled

    Glad to help, Nat - thanks for the rep.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto copy rows to different sheet when specific column is filled
    By AgustSig in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-20-2014, 10:34 AM
  2. Copy complete rows matching criteria to another sheet automatically
    By abdulahadzafar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2014, 12:09 PM
  3. Replies: 2
    Last Post: 01-08-2014, 04:41 PM
  4. Replies: 1
    Last Post: 08-21-2012, 11:41 AM
  5. Replies: 5
    Last Post: 06-21-2012, 05:16 PM
  6. Copy rows with criteria to different sheet & automatically add formula
    By Andrea C in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2009, 01:21 PM
  7. [SOLVED] how do i copy only the coloured filled rows in excel to new sheet
    By Kate at work in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2006, 01:15 AM

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