software

I have workbook with two sheets. It's an order form that needs to
automatically insert rows as each row gets populated.
Sheet 1 is the quot;order buildquot; sheet in which a user will enter a part
number and that part's description, price, amp; 8 other parameters appear.

I am successfully using vlookup to pull data from the 'data sheetquot;.

Basic layout
|-------------------------------------------------------------------|
| part number -|---Qty --|---Price -|---Ext. Price--|-- Description |
|--------------|---------|----------|---------------|---------------|
| B345 | 5 | $5.60 | $28.00 | Glass tube |
|--------------|---------|----------|---------------|---------------|
| B350 | 3 | $4.20 | $12.60 | Glass bowl |
|--------------|---------|----------|---------------|---------------|
|--ROW3--------|---------|----------|---------------|---------------|
|--ROW4--------|---------|----------|---------------|---------------|
|--------------|---------|----------|---------------|---------------|
ROWN
----------------------------------------------------------------------
| Total price: | $40.60 | |

----------------------------------------------------------------------

Part number and Qty: Enterd by user
Price amp; Description: Pulled from data sheet via vlookup formula.
This all works.

What I am trying to do is to automatically increase the rows each time
one row is populated with the same format and formulas.
I can pre-format a given number of rows (i.e. 20 rows) with the format
and vlookup formulas, but some users will need only 5 rows, while
others need 30 rows.

I also want the Total Price to be at the bottom and be pushed down as
new rows are automatically inserted.

Thanks for your help.
CraigHi,
Try this as a starter which inserts a row whenever column A is changed
i.e part number added.

If an existing part number is changed, it will still add a new line so you
need to consider how you handle this.

My VLOOKUP is of the form:

=IF(ISBLANK($A2),quot; quot;,VLOOKUP($A2,Sheet2!$A$1:$C$6,2))

so it only enters data if the Part Number is present i.e inserted line will
be blank until Part Number is entered

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo exitWSC:
If Target.Row = 1 Then Exit Sub
If Target.Column = 1 Then ' Column A
Rows(Target.Row 1).Insert Shift:=xlDown
Target.Offset(0, 2).Copy Target.Offset(1, 2) ' Column C
Target.Offset(0, 3).Copy Target.Offset(1, 3) ' Column D
Target.Offset(0, 4).Copy Target.Offset(1, 4) ' Column E
End If
exitWSC:
Application.EnableEvents = True
End Subquot; wrote:

gt; I have workbook with two sheets. It's an order form that needs to
gt; automatically insert rows as each row gets populated.
gt; Sheet 1 is the quot;order buildquot; sheet in which a user will enter a part
gt; number and that part's description, price, amp; 8 other parameters appear.
gt;
gt; I am successfully using vlookup to pull data from the 'data sheetquot;.
gt;
gt; Basic layout
gt; |-------------------------------------------------------------------|
gt; | part number -|---Qty --|---Price -|---Ext. Price--|-- Description |
gt; |--------------|---------|----------|---------------|---------------|
gt; | B345 | 5 | $5.60 | $28.00 | Glass tube |
gt; |--------------|---------|----------|---------------|---------------|
gt; | B350 | 3 | $4.20 | $12.60 | Glass bowl |
gt; |--------------|---------|----------|---------------|---------------|
gt; |--ROW3--------|---------|----------|---------------|---------------|
gt; |--ROW4--------|---------|----------|---------------|---------------|
gt; |--------------|---------|----------|---------------|---------------|
gt; ROWN
gt; ----------------------------------------------------------------------
gt; | Total price: | $40.60 | |
gt;
gt; ----------------------------------------------------------------------
gt;
gt; Part number and Qty: Enterd by user
gt; Price amp; Description: Pulled from data sheet via vlookup formula.
gt; This all works.
gt;
gt; What I am trying to do is to automatically increase the rows each time
gt; one row is populated with the same format and formulas.
gt; I can pre-format a given number of rows (i.e. 20 rows) with the format
gt; and vlookup formulas, but some users will need only 5 rows, while
gt; others need 30 rows.
gt;
gt; I also want the Total Price to be at the bottom and be pushed down as
gt; new rows are automatically inserted.
gt;
gt; Thanks for your help.
gt; Craig
gt;
gt;

Here's another play to try which could
auto-produce the desired results in a new sheet2 ..

The table as posted is in the quot;Order Buildquot; Sheet1, cols A to E, with labels
in row1, inputs made in cols A to B, vlookup formulae in cols C to E, from
row2 down to row29 (say). Assume the phrase quot;Total Pricequot; is located within
col C, and it denotes the last line (assume it's in C30)

In Sheet1,
Using an empty col to the right, say col G
Put in G2:
=IF(C2=quot;Total Pricequot;,ROW()*100,IF(A2=quot;quot;,quot;quot;,ROW()))
Copy G2 down to G30,
to cover till the last line, i.e. the quot;Total Pricequot; row
(Leave G1 empty)

In a new Sheet2
Copy gt; paste the same labels from Sheet1 into A1:E1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$G:$G,ROWS($A$1:A1))),quot;quot;, IF(INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0)) =0,quot;quot;,INDEX(Sheet1!A:A,MAT
CH(SMALL(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G, 0))))

Copy A2 across to E2, fill down to say, E30
(cover the same extent as done in col G in Sheet1)

Sheet2 will return the desired results,
with the quot;Total Pricequot; line located just below the last detail line
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
gt; wrote in message oups.com...
gt; I have workbook with two sheets. It's an order form that needs to
gt; automatically insert rows as each row gets populated.
gt; Sheet 1 is the quot;order buildquot; sheet in which a user will enter a part
gt; number and that part's description, price, amp; 8 other parameters appear.
gt;
gt; I am successfully using vlookup to pull data from the 'data sheetquot;.
gt;
gt; Basic layout
gt; |-------------------------------------------------------------------|
gt; | part number -|---Qty --|---Price -|---Ext. Price--|-- Description |
gt; |--------------|---------|----------|---------------|---------------|
gt; | B345 | 5 | $5.60 | $28.00 | Glass tube |
gt; |--------------|---------|----------|---------------|---------------|
gt; | B350 | 3 | $4.20 | $12.60 | Glass bowl |
gt; |--------------|---------|----------|---------------|---------------|
gt; |--ROW3--------|---------|----------|---------------|---------------|
gt; |--ROW4--------|---------|----------|---------------|---------------|
gt; |--------------|---------|----------|---------------|---------------|
gt; ROWN
gt; ----------------------------------------------------------------------
gt; | Total price: | $40.60 | |
gt;
gt; ----------------------------------------------------------------------
gt;
gt; Part number and Qty: Enterd by user
gt; Price amp; Description: Pulled from data sheet via vlookup formula.
gt; This all works.
gt;
gt; What I am trying to do is to automatically increase the rows each time
gt; one row is populated with the same format and formulas.
gt; I can pre-format a given number of rows (i.e. 20 rows) with the format
gt; and vlookup formulas, but some users will need only 5 rows, while
gt; others need 30 rows.
gt;
gt; I also want the Total Price to be at the bottom and be pushed down as
gt; new rows are automatically inserted.
gt;
gt; Thanks for your help.
gt; Craig
gt;
Here's a link to a sample construct:
www.savefile.com/files/4350987
Auto-Expand_Rows_cbrenizer_wks_gen.xls
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--