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.

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

Hi Pete,

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

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

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

Glad to help, Nat - thanks for the rep.

Pete