Copy a row from a sheet to another only when a cell in the row is filled out

[joelnyberg]

Hi Everyone and thanks in forehand for your help

Please see attached sample

I am trying to figure out whether it is possible to automatically copy a row from the sheet "pricelist" to the sheet "offer" if the cell "qty" is filled out in the specific row ???

Hope that you understand what I mean

Thanks
Joel

[Pete_UK]

In the attached file I put this formula in cell A6 of the pricelist sheet:

=IF(E6="","-",MAX(A$5:A5)+1)

and copied it down beyond your data (to row 15) in order to accommodate more data being added. It identifies those records with non-zero values of qty and allocates a unique sequential number to each. I also changed your formula in F6 to this:

=IF(E6="","",E6*D6)

and copied that down.

In the offer sheet I've moved your TOTAL row to row 5 and put this formula in A6:

=IFERROR(MATCH(ROWS($1:1),pricelist!A:A,0),"")

When this is copied down it finds the row where each sequential number in column A of the pricelist can be found. I also have this formula in B6:

=IF($A6="","",INDEX(pricelist!B:B,$A6))

and this can be copied across to retrieve data from each column of your table. Finally, the formulae in A6:F6 can be copied down as far as you need them (to row 15 in the example file).

Try it out by putting a new qty in the pricelist sheet.

Hope this helps.

Pete

[joelnyberg]

Hi Pete

And thanks for your help. I am not sure I understand it all, but I am going to transscribe it all to the real file and see what happens!!

[joelnyberg]

Hi Pete,

I've modified the real excel workbook, and copied your formulas.

I apparently do a mistake somewhere, because it seems that the rows are recognised BUT NOT copied... Do you have an explanation??

Thanks in advance

Rgds
Joel

[Pete_UK]

What do you have in cell B4? Did you remember to put the INDEX formula in there? Note that the one I gave you went into B6 and so referred to the value in $A6 - you will need to change that to $A4.

Once you have the formula in B4 (and remember to use semicolons { ; } rather than the commas { , } that I used), you copy it across to L4, then copy the formulae in A4:L4 down as far as you need them.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of 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

[joelnyberg]

Hi Pete,
And thanks again. I simply forgot the formula along the colums... It works really fine!!!

One thing, I am wondering about. "remember to use semicolons { ; } rather than the commas { , } that I used": Either you are mistaking or Excel 365 makes auto-corrections, because I didn't find commas in your copy of my example !!

[Pete_UK]

Glad to hear that it worked for you.

I attached a copy of the workbook to Post #2 so that it would automatically translate the formulae according to your regional settings, which is what Excel does. However, if you were using the formula that I posted in Post #2 (i.e. copying it from that post) you would need to change the semicolons to commas.

Pete