Copy rows to new sheet if value of cell entered greater than 1

**larryluv1** · 11-06-2008, 11:00 AM

Hello,

To preface, I am an absolute novice with Excel and anything more technical than sorting or math functions, I am lost. With that said, can someone help me (in laymans terms) with what I need to do to accomplish the following:

I have an excel sheet with about 500 lines of information. It is basically a price sheet/order form of our products with columns consisting of description, sku number, model number, retail price, cost and then quantity needed.

What I would like to do is when the value of quantity needed column (H) is entered (ie..2,4,etc.), I would like to have that entire specific row copied to a new sheet so that the new sheet would only have items being ordered. This way when I need to print an order, it will only have items needed rather than the main sheet that has all 500 lines/items.

If this something that can be done with a formula? If so, how?

If you need further details or explanation, please let me know.

I will try and attach my order form so you can view and better understand.

Thank you so much for your help!!

**NBVC** · 11-06-2008, 11:01 AM

Welcome to the forum,

Please first read our forum rules and then amend your title according to Rule 1.

Thanks

**larryluv1** · 11-06-2008, 11:23 AM

sorry, I changed it. Hopefully that is correct now.

**NBVC** · 11-06-2008, 11:34 AM

Thank you.

See attached.

First I counted how many records qualify and entered that into Sheet2!K1 using formula =Countif(Sheet1!H:H,">1")

Then I used an array formula in Sheet2!A2 to capture those records that qualify:

=IF(ROWS($A$1:$A1)>$K$1,"",INDEX(Sheet1!A$14:A$483,SMALL(IF(Sheet1!$H$14:$H$483>1,ROW(Sheet1!$H$14:$H$483)-ROW(Sheet1!$H$14)+1),ROWS($A$1:$A1))))

Which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. The { } brackets appear if done correctly.

Then the formula is copied across the columns and down as far as necessary to get all possible record.

I copied it down 490 rows.

**larryluv1** · 11-06-2008, 11:53 AM

OMG!...Your AWSOME!

One problem though...on some of the lines when you enter a value it is putting the wrong rows....see attached.

**NBVC** · 11-06-2008, 12:25 PM

Try changing the formula in A2 to:

Please Login or Register  to view this content.

confirm it with CTRL+SHIFT+ENTER keys and copy Across to col H, then fill down all the row.

**fentontech** · 11-03-2011, 11:14 AM

I know this is an old post but I am trying to change this example up a bit.

I have made adjustments but I am getting no results and I know I have not done it correctly, Basicaly I am trying to get data from Sheet(AR Aging Report) and move the needed columns to sheet(legals) not bringing over blank rows or rows where there is text in column A. Thank you in advance and if I have not explained well enough please let me know!

**NBVC** · 11-03-2011, 11:21 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

**chris.calica** · 04-04-2013, 02:48 PM

Can you change to formula to copy row if cell value greater than 0 as supposed to 1?

**arlu1201** · 04-04-2013, 02:55 PM

Chris,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.