Need help with dynamic vlookup

chris_i2

New Member
Joined
Mar 6, 2009
Messages
3
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top