CODEWITHSUNDEEP
google-sheets
J.Kosch
J.Kosch

Reputation: 13

Copy from one sheet to another and copy to the next row

I have a Bill of Material.xlsx. I use to track the fabricated parts, purchased parts and electrical parts to make my life better. The BOM has many different sheets, but I will talk about two of them.

The first sheet is Called "Fabricated" where the drawing number and material, quantity and so forth. In that sheet column A8:A500 has a drop down list. In that list Vishay Redline, is my key trigger word.

The second sheet is called "Redline Fabricated". This sheet is were I will paste collect data from "Fabricated" so I can print it to give to the design engineers for changes. The array formula is looking for; "Vishay Redline" as the key lookup work, and then copy Cell 2,3,4, and so on, to the Redline Fabricated sheet. I did make a small change to that Arrayformula from my last post.

Here is my problem: if the Arrayformula find the (trigger work) on, let's say row 27, it will copy that data to the other sheet on row 27. And if the key word is found again on row 29, it will also copy it to row 29. So row 28 is blank. I want to collect data from one page no matter where it's at and paste it one row after the other. So when I print that page, everything will look neat.

The Array formula resides on the Redline Fabricated sheet A8:A500

=ARRAYFORMULA(IFERROR(VLOOKUP("Vishay Redline",Fabricated!A8:W8,{2,3,4,5,8,9,23},0),""))

Upvotes: 1

Views: 73

Answers (1)

Ron Kloberdanz
Ron Kloberdanz

Reputation: 442

Thanks for editing the post, and including the sample sheet. It's much more clear now.

Trying to skip blanks with vlookup is not simple (for me at least). I would do it with a query and importrange.

Try: =query({importrange("__YOUR SS KEY ID HERE__","Fabricated!A8:W")},"select Col2, Col3, Col4, Col5, Col8, Col9, Col23 where Col1 contains 'Vishay Redline'",0).

This should only bring in rows that have 'Vishay Redline' in Column A.

Upvotes: 0

Related Questions