Populate Order worksheet from Quote worksheet

[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 created a column on the "Quote" sheet which is
entitled "ORDERED?". If there is a "Y" in the cell, then I want
columns B, C, D, and G in that particular row of the "Quote" sheet to
populate corresponding cells in the "Order" sheet. For instance, if
there is a Y in A10, I want B2, C2, D2, and G2 on the Quote worksheet
to populate cells A15, C15, D15, and E15 on the Order worksheet.

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. I also want all the
data bunched neatly at the top of the Order worksheet, so the IF
statement I tried did not work properly for this. 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.

 

[JP]

If you just wanted to copy the data from the Quote sheet over to the
Order sheet, use IF formulas to create a cell reference.

For example if you had a sheet named 'Quotes' and another sheet named
'Orders', this formula placed in cell A1 of the Orders worksheet would
check the value of A1 of the Quotes worksheet; if it equaled "Y", it
pulls the value from A2 of the Quotes worksheet; otherwise it remains
blank.

=IF(Quotes!A1="Y",Quotes!A2,"")

Check out http://office.microsoft.com/en-us/excel/HP101023431033.aspx
for more info on creating cell references.


Otherwise you would need to post some sample data for further
assistance.

HTH,

JP

 

[Veronica Johnson]

If you just wanted to copy the data from the Quote sheet over to the
Order sheet, use IF formulas to create a cell reference.

For example if you had a sheet named 'Quotes' and another sheet named
'Orders', this formula placed in cell A1 of the Orders worksheet would
check the value of A1 of the Quotes worksheet; if it equaled "Y", it
pulls the value from A2 of the Quotes worksheet; otherwise it remains
blank.

=IF(Quotes!A1="Y",Quotes!A2,"")

Check outhttp://office.microsoft.com/en-us/excel/HP101023431033.aspx
for more info on creating cell references.

Otherwise you would need to post some sample data for further
assistance.

HTH,

JP









- Show quoted text -

Hi JP,

I already tried the IF statement, but it doesn't bunch the data neatly
at the top. For instance if the below is the Quote sheet:

Order? Qty Part #
Manufacturer Price
Y 1000 STB60NF06L MFR1 $0.79
N 1000 STB60NF06L MFR2 $0.92
N 2500 RST533983S MFR3 $1.19
Y 75 32FRT7321 MFR4 $2.53
N 150 9485L MFR5 $1.07
Y 200 R3427 MFR6 $1.88

Then I want lines 1, 4, and 6 to populate the Order sheet, so it looks
like this:

1000 STB60NF06L MFR1 $0.79
75 32FRT7321 MFR4 $2.53
200 R3427 MFR6 $1.88

When I use the IF statement you mentioned above, I have blank rows on
the Order sheet. Anything else I can do?

 

[T. Valko]

I just checked my inbox and have received your email.

Resend the file in xls format, I don't have Excel 2007 (yet).

 

[JP]

I think I understand. You want to filter out all the rows that have a
"Y" in a certain column and paste them into another worksheet. Is that
correct? You could do this with Autofilter.

1. Select one cell in your data sheet and goto Data>Filter>Autofilter
2. Click the arrow in the "Order?" column
3. Choose "Y" -- you should now have a filtered list of only the rows
with a "Y" in that column.
4. Select the cells, Copy, Select other worksheet, Paste.


HTH,
JP

 

[JP]

Hey Biff, the first time I opened a 2007 workbook, I got prompted to
download the viewer. Did you not get that prompt or did you turn down
the offer?


--JP

 

[T. Valko]

I didn't try to open the file. I don't want the Excel Viewer.

I should be getting Excel 2007 any day so I'll be up-to-speed soon!

--
Biff
Microsoft Excel MVP


Hey Biff, the first time I opened a 2007 workbook, I got prompted to
download the viewer. Did you not get that prompt or did you turn down
the offer?


--JP

 

[Veronica Johnson]

I just checked my inbox and have received your email.

Resend the file in xls format, I don't have Excel 2007 (yet).

--
Biff
Microsoft Excel MVP












- Show quoted text -

Hi Biff, I sent it did you receive??

 

[T. Valko]

Hi Biff, I sent it did you receive??

Yes. My reply is on its way.

 

[Veronica Johnson]

I think I understand. You want to filter out all the rows that have a
"Y" in a certain column and paste them into another worksheet. Is that
correct? You could do this with Autofilter.

1. Select one cell in your data sheet and goto Data>Filter>Autofilter
2. Click the arrow in the "Order?" column
3. Choose "Y" -- you should now have a filtered list of only the rows
with a "Y" in that column.
4. Select the cells, Copy, Select other worksheet, Paste.

HTH,
JP










- Show quoted text -

Below reply was inadvertently sent to author instead of posted here on
Groups...
_____________________________________________________________________
Thanks, JP. What I'm trying to do is to get this to run automatically
so
that when I click a button, it will do it for me. (i.e. "Create
Order"
button automatically puts all the info onto the Order sheet.) I
created a
macro that did exactly what you suggested. However, when there is
only one
item on the Quote sheet, the AutoFilter function won't work properly
since
there's nothing to filter.
_____________________________________________________________________

I was able to find the solution to this thanks to Biff.

Thanks again Biff!

 

[JP]

Glad to hear it!

TTFN,
JP

Below reply was inadvertently sent to author instead of posted here on
Groups...
_____________________________________________________________________
Thanks, JP.  What I'm trying to do is to get this to run automatically
so
that when I click a button, it will do it for me.  (i.e. "Create
Order"
button automatically puts all the info onto the Order sheet.) I
created a
macro that did exactly what you suggested.  However, when there is
only one
item on the Quote sheet, the AutoFilter function won't work properly
since
there's nothing to filter.
_____________________________________________________________________

I was able to find the solution to this thanks to Biff.

Thanks again Biff!- Hide quoted text -

- Show quoted text -