Copy content of cell to another worksheet if condition met with no blanks

[cfeist]

I have searched through the forum and can't quite find the correct formula.

I have a sheet with rows of data. I want to search through a column for any cell that =2 and if true copy the content of the next column (same row) into another worksheet. I was able to make it work with a simple if statement but I get blank rows when the condition is not met. I want the new worksheet to be a simple list of all the values with no blanks.

I am hoping I can do this without requiring a macro.

Hope this makes sense.
Thanks

[Pete_UK]

Attach a sample workbook (the FAQ describes how to), so we can see how your data is laid out.

Pete

[cfeist]

test.xlsx

See the attached simplified example. I am trying to use the content of Column E in the worksheet "datasheet". If the content of Column E is =2 then I want to paste the content of Column F into the new worksheet to get a list. But I don't want any blanks in Sheet1.

[Pete_UK]

The matching column seems to be F in your example file, so put this formula in cell A5 of the datasheet:

=IF(F5=2,MAX(A$4:A4)+1,"-")

then copy down to the bottom of your table. Then put this formula in, say, cell A2 of the other sheet:

=IFERROR(INDEX(Datasheet!G:G,MATCH(ROWS($1:1),Datasheet!A:A,0)),"-")

and copy that down as far as you think you need it (until you get hyphens showing).

Hope this helps.

Pete

[cfeist]

Thanks Pete,

That seems to have solved it for me. I had to modify it slightly for the correct columns but otherwise it was perfect.

Christina

[Pete_UK]

Well, that's good to hear - glad to be able to help.

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, I would like to inform you 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

[cfeist]

Thanks again. I set this to solved and I believe I was able to add to your reputation. Thanks for the fast help.

[Pete_UK]

You're welcome, and thanks for tidying up the thread. Come back anytime.

Pete