Quotation populate date from price list?

**smartcard** · 04-20-2010, 09:19 AM

I have an Excel file with two sheets...
1) Price List
2) Quotation

I need to populate the items with its prices in the "(2) Quotation" automatically based on the QTY value in the "(1) Price List" sheet.

You can see in the attached sample there are 6 items but only 3 of them are having value in the QTY, so I need these 3 items to be displayed in the "2) Quotation" sheet

I need your help please.

**6StringJazzer** · 04-20-2010, 11:49 AM

I would take a different approach. One approach is to just use one worksheet instead of two. You are entering quantities specific to on order onto a master data sheet; you can just use filtering on that sheet to show only those items with quantity >0.

The other choice is to leave your data sheet alone; you don't want to pollute master data with data specific to one order. Then you can populate the quotation sheet based on the data sheet. Use data validation, with list, to select items. and do lookups for part name and price.

I have included both methods in the attached.

BTW I recommend you add an Extended Price column for quantity * unit price.

**smartcard** · 04-21-2010, 01:22 AM

Originally Posted by 6StringJazzer

I would take a different approach. One approach is to just use one worksheet instead of two. .

Thanks for this suggestion, but my aim is to allow only in the data sheet to allow typing the QTY and which should populate the quote sheet.

Is there any way out for this?

**6StringJazzer** · 04-21-2010, 09:28 AM

I'm pretty sure there's a way to do this with formulas but I haven't done a lot of that. I would tend to fall back on VBA in this situation. I will look at a VBA solution when I get time, though someone else may be able to offer a formula-based solution in the meantime.