# Copy values from one sheet to another based on criteria

1. ## Copy values from one sheet to another based on criteria

Hi!

I have a workbook (sample attached) that has two sheets. I want the second sheet to be based on values from the master sheet. In this case it would be columns A, B, C, F, and G but only if there is a value in column F.

Thanks.

Ian

2. ## Re: Copy values from one sheet to another based on criteria

Put this ARRAY formula in A2...
=IFERROR(INDEX(Master!\$A:\$G,SMALL(IF(Master!\$F\$2:\$F\$11<>"",ROW(Master!\$A\$2:\$A\$11)),ROWS(\$A\$1:A1)),MATCH(A\$1,Master!\$A\$1:\$G\$1,0)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across, then copy all down as needed

3. ## Re: Copy values from one sheet to another based on criteria

Thanks!

Exactly what I needed.

Ian

4. ## Re: Copy values from one sheet to another based on criteria

Happy to help and thanks for the feedback

5. ## Re: Copy values from one sheet to another based on criteria

Sorry but I still have a minor problem with this.

If you look at Sheet1 then column D is not showing results from column AB of the master.

Data in the spreadsheet is fictitious so no problems posting here.

Ian

6. ## Re: Copy values from one sheet to another based on criteria

Thats because the headings are not the same. Sheet1 D says Crew, but Master says Assigned crew. Make the headings the same and it will work

7. ## Re: Copy values from one sheet to another based on criteria

And so it does!

Thanks again - much appreciated.

Ian

8. ## Re: Copy values from one sheet to another based on criteria

No problem

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

#### 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