Creating order form in Excel 2007; Need to copy entire row if one cell has data

[moonpie71]

Good afternoon,

I'm trying to create a very simple order form, and would appreciate some assistance. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)

I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)

Excel 2007

	A	B	C	D	E	F
BX-2B1324X	0.9% NaCL 1000ml Bags
AB-1966-07	0.9% NaCL 30ml MDV 25s
KE-5150	Alcohol Preps MED 200s
DY-1113	Alcohol Preps MED STR 200s
NUT-99990	Bandage Cloth 1x3 100s
NUT-99991	Bandage Cloth 3/4x3 100s
NUT-1595033	Bandage Cloth Flexible

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Item Number[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]CS[/TD]
[TD="align: right"]$23.52[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]$117.60[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]PK[/TD]
[TD="align: right"]$19.22[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$38.44[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]BX[/TD]
[TD="align: right"]$1.22[/TD]
[TD="align: center"][/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]BX[/TD]
[TD="align: right"]$1.30[/TD]
[TD="align: center"][/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]BX[/TD]
[TD="align: right"]$2.73[/TD]
[TD="align: center"][/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]EA[/TD]
[TD="align: right"]$6.08[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$12.16[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]BX[/TD]
[TD="align: right"]$2.34[/TD]
[TD="align: center"][/TD]
[TD="align: right"]$0.00[/TD]

</tbody>

Common Items​

Excel 2007

	A	B	C	D	E	F
Name:
Deliver to:
BX-2B1324X	0.9% NaCL 1000ml Bags
AB-1966-07	0.9% NaCL 30ml MDV 25s
NUT-99991	Bandage Cloth 3/4x3 100s

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Item Number[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]CS[/TD]
[TD="align: right"]$23.52[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]$117.60[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]PK[/TD]
[TD="align: right"]$19.22[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$38.44[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]EA[/TD]
[TD="align: right"]$6.08[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$12.16[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Order​

I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print. Thanks in advance for your kind assistance and reply!

Nancy

 

[Skybluekid]

Am I right in saying that the user will in put a number in column E on the Order form sheet and then copy the row from copy the relevant row on the Common items? If so, I can see a big flaw as in row 3 and 7 on the Common Items both have a 2 as a quantity. How will do separate these two items?

Would it be easier for the user to select the item number and then copy the row from there?

 

[moonpie71]

Am I right in saying that the user will in put a number in column E on the Order form sheet and then copy the row from copy the relevant row on the Common items? If so, I can see a big flaw as in row 3 and 7 on the Common Items both have a 2 as a quantity. How will do separate these two items?

Would it be easier for the user to select the item number and then copy the row from there?

The only column that the user will enter data is Column E. If they need two boxes of the item on row 4, and 2 boxes of the item on row 12, and 7 boxes of the item on row 32, then I need (the entire) rows 4, 12 and 32 copied over. I only entered quantities in Column E as an example.

Thanks for your reply!

 

[Skybluekid]

Excel Workbook
	A	B	C	D	E	F	G
1	User Form
2		Item Number	Description	Unit	Price	Qty	Total
3		BX-2B1324X	0.9% NaCL 1000ml Bags	CS	$23.52	1	$117.60
4		AB-1966-07	0.9% NaCL 30ml MDV 25s	PK	$19.22		$38.44
5		KE-5150	Alcohol Preps MED 200s	BX	$1.22		$0.00
6		DY-1113	Alcohol Preps MED STR 200s	BX	$1.30	3	$0.00
7		NUT-99990	Bandage Cloth 1x3 100s	BX	$2.73		$0.00
8		NUT-99991	Bandage Cloth 3/4x3 100s	EA	$6.08		$12.16
9		NUT-1595033	Bandage Cloth Flexible	BX	$2.34	4	$0.00
10
11	Order Sheet
12
13		BX-2B1324X	0.9% NaCL 1000ml Bags	CS	$23.52	1	$117.60
14		DY-1113	Alcohol Preps MED STR 200s	BX	$1.30	3	$0.00
15		NUT-1595033	Bandage Cloth Flexible	BX	$2.34	4	$0.00
Sheet1

Copy the formula across and change the index range

 

[moonpie71]

Spreadsheet Formulas

Cell Formula B13 {=IF(ROWS($B$13:B13)>COUNTA($F$3:$F$9),"",INDEX($B$3:$B$9,SMALL(IF($F$3:$F$9<>"",ROW($B$3:$B$9)-ROW($B$3)+1),ROWS($B$13:B13))))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Copy the formula across and change the index range

Holy cow, you're smart. Am I correct that I paste this in the "Order" worksheet? My "Common Items" worksheet data (not including headers) is in area A2:F76. When I paste the formula in B13 of "Order", and copy it across, the row fills with the word "Description". I'll keep playing with it to see if I can figure it out on my own.

Thanks so much!

 

[Skybluekid]

No problem at all, glad to help.

Yes, you need this on the Order worksheet. The best way would be create named ranges for
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: center"]ItemNumber
[/TD]
[TD="align: center"]Description
[/TD]
[TD="align: center"]Unit
[/TD]
[TD="align: center"]Price
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Total columns.
[/TD]
[/TR]
</tbody>[/TABLE]

This will make it easier for you to insert it into the Index array.

Any problems let me know.

 

[morganita]

I'm a newbie to IF formulas and also creating the same order form in a new worksheet. I'm testing above table as example, but your provided formula is not working for me. What am I doing wrong?

 

[Skybluekid]

You need to use Control+Shift+Enter to apply the formula. This is becuase the Fnction IF can not handle an array by itself, it is an array formula. If you are still having problems post what you have and I try to help.

 

[morganita]

I have also tried Control+Shift+Enter. I pasted the formula on the 'order form' worksheet and changed the cell numbers.

In 'catalogue' worksheet I have from A1 cel Item Number till F8 cel $0.00. In the second worksheet 'order form' I have the headers from B3 to G3 on a row Item Number - Description - Unit - Price - Qty - Total. On B4 the pasted formula {=IF(ROWS($B$4:B4)>COUNTA($F$2:$F$8),"",INDEX($B$2:$B$8,SMALL(IF($F$2:$F$8<>"",ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$4:B4))))}

 

[morganita]

Here is my excel file in which I want to create registration form and a order form. http://we.tl/WycfOaHx9n
I want to keep up al the orders in the registration sheet with IF out of the catalogue sheet. Thereafter when every new order is made in the registration sheet I want a order form to be printed out. Is it possible to create buttons like 'make' and 'print' in the sheets?