vlookup?

[Veronica Johnson]

I have 2 worksheets in the same workbook. One worksheet is for the
price quote to the customer and has many rows, each item/price/
quantity in its own row. The other worksheet is for the actual order
sheet which the customer has purchased based on the quoted items.
However, the customer doesn't usually buy every item on the quote
sheet. So, what I've done is, I've created a column on the "Quote"
sheet which is entitled "ORDERED?". If there is a "Y" in the cell,
then I want some of the cells in that particular row of the "Quote"
sheet to populate corresponding cells in the "Order" sheet. However,
if there is a "N" in the cell, then I don't want any of the
information to be copied to the "Order" sheet. Is this possible?
I've tried to be as descriptive and as thorough as possible in my
explanation and any help would be greatly appreciated. Thank you so
much.

 

[T. Valko]

Have you considered using AutoFilter? This would be the easiest approach.

Filter the column ORDERED? by "Y" then copy the data to the other sheet.

I'm assuming you want to "compile" or make a "summary" of all ordered items.

You *could* use formulas but it depends on how much data needs to be pulled
into the Order sheet and how much data needs to be "searched" on the Quote
sheet as to whether a formula solution is suitable.

 

[Veronica Johnson]

Have you considered using AutoFilter? This would be the easiest approach.

Filter the column ORDERED? by "Y" then copy the data to the other sheet.

I'm assuming you want to "compile" or make a "summary" of all ordered items.

You *could* use formulas but it depends on how much data needs to be pulled
into the Order sheet and how much data needs to be "searched" on the Quote
sheet as to whether a formula solution is suitable.

--
Biff
Microsoft Excel MVP






- Show quoted text -

I tried the "Autofilter" function, but what I need is a little
different. Both sheets have: (Quantity), (Manufacturer), (Part#),
(Price) columns in common. I need ONLY the items which are marked
with a "Y" in the "Ordered?" column to populate the corresponding
cells in the "Order" sheet.

When I use the autofilter to select only the items with a "Y" in the
"Ordered?" column, the "Order" sheet still captures ALL of the items
quoted, whether they have been ordered or not.

Is there a way to have ONLY the items which have been ordered (that
is, a "Y" in the "Ordered?" column) populate the "Order" sheet without
having blank rows between items?

I've tried to use a formula, however, the formula I used only copies
from the specific row which has been ordered.
For instance: On the "Quote" sheet, the items in Rows 1,3,4,5,7,9
might have been the items ordered. However, I want the item in Row 3
of the "Quote" sheet to move to Row 2 of the "Order" sheet. Then I
want Quote: row 4 to move to Order: row 3. Quote: row 5 to Order: row
4. Quote: row 7 to Order: row 5. Quote: row 9 to Order: row
6............

I hope this explains enough of what I'm looking for. I really do
appreciate your help.

 

[T. Valko]

Have you considered using AutoFilter? This would be the easiest approach.

Filter the column ORDERED? by "Y" then copy the data to the other sheet.

I'm assuming you want to "compile" or make a "summary" of all ordered
items.

You *could* use formulas but it depends on how much data needs to be
pulled
into the Order sheet and how much data needs to be "searched" on the Quote
sheet as to whether a formula solution is suitable.

--
Biff
Microsoft Excel MVP






- Show quoted text -

I tried the "Autofilter" function, but what I need is a little
different. Both sheets have: (Quantity), (Manufacturer), (Part#),
(Price) columns in common. I need ONLY the items which are marked
with a "Y" in the "Ordered?" column to populate the corresponding
cells in the "Order" sheet.

When I use the autofilter to select only the items with a "Y" in the
"Ordered?" column, the "Order" sheet still captures ALL of the items
quoted, whether they have been ordered or not.

Is there a way to have ONLY the items which have been ordered (that
is, a "Y" in the "Ordered?" column) populate the "Order" sheet without
having blank rows between items?

I've tried to use a formula, however, the formula I used only copies
from the specific row which has been ordered.
For instance: On the "Quote" sheet, the items in Rows 1,3,4,5,7,9
might have been the items ordered. However, I want the item in Row 3
of the "Quote" sheet to move to Row 2 of the "Order" sheet. Then I
want Quote: row 4 to move to Order: row 3. Quote: row 5 to Order: row
4. Quote: row 7 to Order: row 5. Quote: row 9 to Order: row
6............

I hope this explains enough of what I'm looking for. I really do
appreciate your help.
Can you send a copy of the file to me? If so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

 

[Veronica Johnson]

I tried the "Autofilter" function, but what I need is a little
different.  Both sheets have: (Quantity), (Manufacturer), (Part#),
(Price) columns in common.  I need ONLY the items which are marked
with a "Y" in the "Ordered?" column to populate the corresponding
cells in the "Order" sheet.

When I use the autofilter to select only the items with a "Y" in the
"Ordered?" column, the "Order" sheet still captures ALL of the items
quoted, whether they have been ordered or not.

Is there a way to have ONLY the items which have been ordered (that
is, a "Y" in the "Ordered?" column) populate the "Order" sheet without
having blank rows between items?

I've tried to use a formula, however, the formula I used only copies
from the specific row which has been ordered.
For instance:  On the "Quote" sheet, the items in Rows 1,3,4,5,7,9
might have been the items ordered.  However, I want the item in Row 3
of the "Quote" sheet to move to Row 2 of the "Order" sheet.  Then I
want Quote: row 4 to move to Order: row 3.  Quote: row 5 to Order: row
4.  Quote: row 7 to Order: row 5.  Quote: row 9 to Order: row
6............

I hope this explains enough of what I'm looking for.  I really do
appreciate your help.



Can you send a copy of the file to me? If so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Hi Biff,

I actually figured out a way to do what I wanted with the Quote sheet
and Order sheet by creating a macro. I'm sure there is another way to
do it, but with my limited knowledge of Excel, I just went with what
worked. However, I still could use your help with one more thing...

On the Worksheet tab, I will ultimately have quotes from one or more
different vendors.

I want the vendor (columns i,K,M,O,Q) with the lowest price (columns
J,L,N,P,R) from the Worksheet to populate over to my Order sheet.

So the vendor from Worksheet populates the Vendor column (column G) on
the Order sheet, and the price from that vendor populates the lowest
price column (column i). I thought I could do a VLOOKUP by
referencing the part number, but I don't know how to make it find the
lowest price in that row.

The price for each vendor is to the right. For instance, the vendor
in cell K2 would have their price in L2.

How can I do this? You've been such a terrific help.

 

[T. Valko]

Have you considered using AutoFilter? This would be the easiest
approach.

Filter the column ORDERED? by "Y" then copy the data to the other sheet.

I'm assuming you want to "compile" or make a "summary" of all ordered
items.

You *could* use formulas but it depends on how much data needs to be
pulled
into the Order sheet and how much data needs to be "searched" on the
Quote
sheet as to whether a formula solution is suitable.

- Show quoted text -

I tried the "Autofilter" function, but what I need is a little
different. Both sheets have: (Quantity), (Manufacturer), (Part#),
(Price) columns in common. I need ONLY the items which are marked
with a "Y" in the "Ordered?" column to populate the corresponding
cells in the "Order" sheet.

When I use the autofilter to select only the items with a "Y" in the
"Ordered?" column, the "Order" sheet still captures ALL of the items
quoted, whether they have been ordered or not.

Is there a way to have ONLY the items which have been ordered (that
is, a "Y" in the "Ordered?" column) populate the "Order" sheet without
having blank rows between items?

I've tried to use a formula, however, the formula I used only copies
from the specific row which has been ordered.
For instance: On the "Quote" sheet, the items in Rows 1,3,4,5,7,9
might have been the items ordered. However, I want the item in Row 3
of the "Quote" sheet to move to Row 2 of the "Order" sheet. Then I
want Quote: row 4 to move to Order: row 3. Quote: row 5 to Order: row
4. Quote: row 7 to Order: row 5. Quote: row 9 to Order: row
6............

I hope this explains enough of what I'm looking for. I really do
appreciate your help.



Can you send a copy of the file to me? If so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Hi Biff,

I actually figured out a way to do what I wanted with the Quote sheet
and Order sheet by creating a macro. I'm sure there is another way to
do it, but with my limited knowledge of Excel, I just went with what
worked. However, I still could use your help with one more thing...

On the Worksheet tab, I will ultimately have quotes from one or more
different vendors.

I want the vendor (columns i,K,M,O,Q) with the lowest price (columns
J,L,N,P,R) from the Worksheet to populate over to my Order sheet.

So the vendor from Worksheet populates the Vendor column (column G) on
the Order sheet, and the price from that vendor populates the lowest
price column (column i). I thought I could do a VLOOKUP by
referencing the part number, but I don't know how to make it find the
lowest price in that row.

The price for each vendor is to the right. For instance, the vendor
in cell K2 would have their price in L2.

How can I do this? You've been such a terrific help.

See your other post