Need help with dynamic vlookup

[chris_i2]

I am building a quoting program to use in excel.
I will have fields - <TABLE style="WIDTH: 402pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=534 border=0><COLGROUP><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 28pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=37 height=22>ITEM</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=69>BRAND</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=117>PART #</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 127pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=169>MATERIAL</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78>QTY</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Sell price</TD></TR></TBODY></TABLE>
What I would like to have is this be a dynamic table that I can add additional items and not have to modify formulas after they are created.
All QTY fields will be 0 until I increase for the quote when I increase I would like for worksheet 2 to populate a area with the QTY field and Material field so it will create the quote form while I am adding items.
and at the bottom of the quote page (worksheet 2) I can just move the total field from sheet 1 to sheet 2.

Any help would be great I have struggled with this for 4 days now so I have put in alot of effort with no luck!

Thank you in advance.
Chris

 

[Greg Truby]

What exactly are you having problems with? Getting the table to be dynamic? While you can always use a named formula to create a dynamic range, it's simpler to use the built in tools. If getting the table to be dynamic is your problem and you are using Excel 2007, just create a table or in Excel 2003, create a list and it will resize as needed. In 2007, Ctrl+T or Ctrl+L will create a table. In 2003, Ctrl+L. In the case of 2003, you still need to name the list for easy reference. In 2007 the table will be named Table1, which you can edit using the Table Tools | Design tab.

If this is not your sticking point, please clarify your problem.

 

[chris_i2]

I am having problems with the formula to move the data where the qty changes from 0 to any other number it then moves the cells that I want to show to sheet 2 so it will create an order/quote sheet. I am using excel 2007

Thanks for the input.

Chris

 

[chris_i2]

Anyone have any ideas?

Thanks
Chris

 

[Greg Truby]

I'm pretty sure I now understand exactly what you want. I just don't have time today (at work) to try and show you how. I am 100% certain I have seen Aladin solve exactly this kind of problem on several occasions. The trick would be to find the thread! You might see if you can get lucky with a search.