Fill automatically from one worksheet to another based on cel valu

[guillaumet]

Hi,

I did some search whithin the forum and looked at a few formulas (i.e.
VLOOKUP...) but couldn't find a solution.

I am creating a budgetary quote generator document, which consists of 2
worksheets. The first worksheet "PRICE-LIST" contains a list of all items for
sale by my company. You just have to enter the quantity desired for each
items wanted.
For each row I have an product item with it's part #, description, price...

The second worksheet "QUOTE" is just a budgetary quote, that gives a total $
amount. All I want is excel to fill the QUOTE worksheet automatically will
all products selected in the "PRICE-LIST" (quantity value >= 1) and to be
able to fill a line for each product. I would remove the price list
information (for example) in the QUOTE worksheet.

Below is an example of what it would look like. Quantity is the only field
to fill manually. The rest should be automatic.

--------------------- PRICE-LIST worksheet
------------------------------------------
Part# Name Description Unit Price Quantity Total
111 ABC blablablal $10 1 $10
112 DEF blablablal $20 0
113 GHI blablablal $5 2 $10

--------------------- QUOTE worksheet
------------------------------------------

Dear Mr. John,

the total for this project is: $20
This includes the following items:

Part# Name Description Quantity
111 ABC blablablal 1
113 GHI blablablal 2

 

[Biff]

Hi!

Create named ranges for the following on the price list sheet:

Part#
Name
Description
Quantity

Use those headers as the names. The # char is not an acceptable char for a
defined name so you'd have to name that something else like Part or PartNo.

On the quote sheet you have the headers:

Part Name Description Quantity

These headers are also the names of the named ranges that refer to the
corresponding column on the price sheet.

Assume those headers are in the range A1010 on the quote sheet.

In A11 enter this formula with the key combo of CTRL,SHIFT,ENTER

=IF(ISERROR(SMALL(IF(Quantity>0,ROW($1:$3)),ROW(1:1))),"",INDEX(INDIRECT(A$10),SMALL(IF(Quantity>0,ROW($1:$3)),ROW(1:1))))

Copy across to D1 then down until you get blanks.

If you're not following this example and need further help I will do it for
you if you want to send me your file.

Biff

 

[guillaumet]

Biff,

thanks a lot ! I tried but unsuccessful. I also tried to email the file, but
your email address is not valid.

 

[Biff]

Hi!

Try this:

xl can help at comcast period net

Remove can and change the obvious.

Biff

 

[guillaumet]

Actually,

In the meantime I found a Hide macro (VB script) in the forum, I've
implemented in my execel and iT works great. Thanks anyhow for your help.