Fill automatically from one worksheet to another based on cel valu

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(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" <[email protected]> wrote in message
news:[email protected]...
> 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(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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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(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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>