|
Populate Order worksheet from Quote worksheet
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. |
Populate Order worksheet from Quote worksheet
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/ex...023431033.aspx for more info on creating cell references. Otherwise you would need to post some sample data for further assistance. HTH, JP On Jan 22, 3:57*pm, Veronica Johnson wrote: 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. |
Populate Order worksheet from Quote worksheet
On Jan 22, 4:25*pm, JP wrote:
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 On Jan 22, 3:57*pm, Veronica Johnson wrote: 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.- Hide quoted text - - 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? |
Populate Order worksheet from Quote worksheet
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 "Veronica Johnson" wrote in message ... 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. |
Populate Order worksheet from Quote worksheet
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 DataFilterAutofilter 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 On Jan 22, 4:44*pm, Veronica Johnson wrote: On Jan 22, 4:25*pm, JP wrote: 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 On Jan 22, 3:57*pm, Veronica Johnson wrote: 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.- Hide quoted text - - 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?- Hide quoted text - - Show quoted text - |
Populate Order worksheet from Quote worksheet
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 On Jan 22, 4:51*pm, "T. Valko" wrote: 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 |
Populate Order worksheet from Quote worksheet
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 "JP" wrote in message ... 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 On Jan 22, 4:51 pm, "T. Valko" wrote: 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 |
Populate Order worksheet from Quote worksheet
On Jan 22, 4:51*pm, "T. Valko" wrote:
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 "Veronica Johnson" wrote in message ... 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.- Hide quoted text - - Show quoted text - Hi Biff, I sent it did you receive?? |
Populate Order worksheet from Quote worksheet
On Jan 22, 4:51 pm, "T. Valko" wrote:
I just checked my inbox and have received your email. Resend the file in xls format, I don't have Excel 2007 (yet). "Veronica Johnson" wrote in message Hi Biff, I sent it did you receive?? Yes. My reply is on its way. -- Biff Microsoft Excel MVP |
Populate Order worksheet from Quote worksheet
On Jan 22, 5:15*pm, JP wrote:
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 DataFilterAutofilter 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 On Jan 22, 4:44*pm, Veronica Johnson wrote: On Jan 22, 4:25*pm, JP wrote: 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 On Jan 22, 3:57*pm, Veronica Johnson wrote: 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.- Hide quoted text - - 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?- Hide quoted text - - Show quoted text -- Hide quoted text - - 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! |
Populate Order worksheet from Quote worksheet
Glad to hear it!
TTFN, JP On Jan 23, 3:50*pm, Veronica Johnson wrote: On Jan 22, 5:15*pm, JP wrote: 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 DataFilterAutofilter 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 On Jan 22, 4:44*pm, Veronica Johnson wrote: On Jan 22, 4:25*pm, JP wrote: 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 On Jan 22, 3:57*pm, Veronica Johnson wrote: 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.- Hide quoted text - - 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?- Hide quoted text - - Show quoted text -- Hide quoted text - - 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!- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 05:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com