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

----------------------------------------------------------------------------------


Thanks again for anyone's help ! I appreciate

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 A10:D10 on the quote sheet.

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

=IF(ISERROR(SMALL(IF(Quantity0,ROW($1:$3)),ROW(1: 1))),"",INDEX(INDIRECT(A$10),SMALL(IF(Quantity0,R OW($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" wrote in message
...
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

----------------------------------------------------------------------------------


Thanks again for anyone's help ! I appreciate

Biff,

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

"Biff" wrote:

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 A10:D10 on the quote sheet.

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

=IF(ISERROR(SMALL(IF(Quantity0,ROW($1:$3)),ROW(1: 1))),"",INDEX(INDIRECT(A$10),SMALL(IF(Quantity0,R OW($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" wrote in message
...
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

----------------------------------------------------------------------------------


Thanks again for anyone's help ! I appreciate

Hi!

Try this:

xl can help at comcast period net

Remove can and change the obvious.

Biff

"guillaumet" wrote in message
...
Biff,

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

"Biff" wrote:

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 A10:D10 on the quote sheet.

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

=IF(ISERROR(SMALL(IF(Quantity0,ROW($1:$3)),ROW(1: 1))),"",INDEX(INDIRECT(A$10),SMALL(IF(Quantity0,R OW($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" wrote in message
...
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

----------------------------------------------------------------------------------


Thanks again for anyone's help ! I appreciate

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.

"Biff" wrote:

Hi!

Try this:

xl can help at comcast period net

Remove can and change the obvious.

Biff

"guillaumet" wrote in message
...
Biff,

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

"Biff" wrote:

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 A10:D10 on the quote sheet.

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

=IF(ISERROR(SMALL(IF(Quantity0,ROW($1:$3)),ROW(1: 1))),"",INDEX(INDIRECT(A$10),SMALL(IF(Quantity0,R OW($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" wrote in message
...
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

----------------------------------------------------------------------------------


Thanks again for anyone's help ! I appreciate