auto expand rows

[cbrenizer]

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 "order build" sheet in which a user will enter a part
number and that part's description, price, & 8 other parameters appear.

I am successfully using vlookup to pull data from the 'data sheet".

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 & 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.
Craig

 

[Guest]

Hi,
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)," ",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 Sub

 

[Max]

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

The table as posted is in the "Order Build" 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 "Total Price" 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="Total Price",ROW()*100,IF(A2="","",ROW()))
Copy G2 down to G30,
to cover till the last line, i.e. the "Total Price" row
(Leave G1 empty)

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

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$G:$G,ROWS($A$1:A1))),"",IF(INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0))=0,"",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 "Total Price" line located just below the last detail line

--

 

[Max]

Here's a link to a sample construct:
http://www.savefile.com/files/4350987
Auto-Expand_Rows_cbrenizer_wks_gen.xls