| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have to work books one with pricing and the other the actual quotes.
In some cases the prices are linked from the price book to the quote book.
If I make changes added lines to the price book and the quote book is not opened the the quote books will not update correctly. Correct? If both books are open I can get around this. Correct? I tried getting some help with a macro that would do this but I haven't had any but I think that this would solve the problem. I would like to use a combo box that is populated by all the matches in column B and than have other cell in the same row bet filled with the price, cost and time to install. Any suggestions? I know hire a professional!!!! Marc:) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have to work books one with pricing and the other the actualquotes.
I think I would use/add a key column that I can use to match up with the price
book. For instance, if the user chooses a nice unique item/description/part number in the Quote workbook, you could use formulas to retrieve the data that you want. In general, it'll look something like: =IF(A1="","",IF(ISERROR(VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE)),"Missing", VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE))) (that's with the "sending" workbook open.) If you close that other workbook, excel will adjust the formula to include the paths: =IF(A1="","",IF(ISERROR( VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE)), "Missing", VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE))) You may want to read Debra Dalgleish's notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) Marc wrote: In some cases the prices are linked from the price book to the quote book. If I make changes added lines to the price book and the quote book is not opened the the quote books will not update correctly. Correct? If both books are open I can get around this. Correct? I tried getting some help with a macro that would do this but I haven't had any but I think that this would solve the problem. I would like to use a combo box that is populated by all the matches in column B and than have other cell in the same row bet filled with the price, cost and time to install. Any suggestions? I know hire a professional!!!! Marc:) -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have to work books one with pricing and the other the actual quotes.
Can I use VLOKUP to match a list of items to populate a combo box? Say
"Speakers in-ceiling". If so than I can use "Match" if the item is chosen to populate the cost, price and time to install cells. Marc "Dave Peterson" wrote in message ... I think I would use/add a key column that I can use to match up with the price book. For instance, if the user chooses a nice unique item/description/part number in the Quote workbook, you could use formulas to retrieve the data that you want. In general, it'll look something like: =IF(A1="","",IF(ISERROR(VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE)),"Missin g", VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE))) (that's with the "sending" workbook open.) If you close that other workbook, excel will adjust the formula to include the paths: =IF(A1="","",IF(ISERROR( VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE)), "Missing", VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE))) You may want to read Debra Dalgleish's notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) Marc wrote: In some cases the prices are linked from the price book to the quote book. If I make changes added lines to the price book and the quote book is not opened the the quote books will not update correctly. Correct? If both books are open I can get around this. Correct? I tried getting some help with a macro that would do this but I haven't had any but I think that this would solve the problem. I would like to use a combo box that is populated by all the matches in column B and than have other cell in the same row bet filled with the price, cost and time to install. Any suggestions? I know hire a professional!!!! Marc:) -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have to work books one with pricing and the other the actualquotes.
=vlookup() will return a single value to the cell with the formula.
Marc wrote: Can I use VLOKUP to match a list of items to populate a combo box? Say "Speakers in-ceiling". If so than I can use "Match" if the item is chosen to populate the cost, price and time to install cells. Marc "Dave Peterson" wrote in message ... I think I would use/add a key column that I can use to match up with the price book. For instance, if the user chooses a nice unique item/description/part number in the Quote workbook, you could use formulas to retrieve the data that you want. In general, it'll look something like: =IF(A1="","",IF(ISERROR(VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE)),"Missin g", VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE))) (that's with the "sending" workbook open.) If you close that other workbook, excel will adjust the formula to include the paths: =IF(A1="","",IF(ISERROR( VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE)), "Missing", VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE))) You may want to read Debra Dalgleish's notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) Marc wrote: In some cases the prices are linked from the price book to the quote book. If I make changes added lines to the price book and the quote book is not opened the the quote books will not update correctly. Correct? If both books are open I can get around this. Correct? I tried getting some help with a macro that would do this but I haven't had any but I think that this would solve the problem. I would like to use a combo box that is populated by all the matches in column B and than have other cell in the same row bet filled with the price, cost and time to install. Any suggestions? I know hire a professional!!!! Marc:) -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have to work books one with pricing and the other the actual
First I would recommend that you have ONE base sheet where all changes are
made, al products and all of there details. Then, each workbook you have would have one page that only cares about opening A1, A2,A3, etc. Whatever is in there. They you do Vlookup against that table, not the master. My master product wheet is about 300 lines, and has different characteristics over to about BT. The workbook with my product quotes has a second sheet with the details it needs, the shipping sheet with what it needs, my purchase orders have what they need, etc. Just makesure the dependent workbooks are closed when you add lines in the middle or sort the master. "Dave Peterson" wrote: =vlookup() will return a single value to the cell with the formula. Marc wrote: Can I use VLOKUP to match a list of items to populate a combo box? Say "Speakers in-ceiling". If so than I can use "Match" if the item is chosen to populate the cost, price and time to install cells. Marc "Dave Peterson" wrote in message ... I think I would use/add a key column that I can use to match up with the price book. For instance, if the user chooses a nice unique item/description/part number in the Quote workbook, you could use formulas to retrieve the data that you want. In general, it'll look something like: =IF(A1="","",IF(ISERROR(VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE)),"Missin g", VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE))) (that's with the "sending" workbook open.) If you close that other workbook, excel will adjust the formula to include the paths: =IF(A1="","",IF(ISERROR( VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE)), "Missing", VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE))) You may want to read Debra Dalgleish's notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) Marc wrote: In some cases the prices are linked from the price book to the quote book. If I make changes added lines to the price book and the quote book is not opened the the quote books will not update correctly. Correct? If both books are open I can get around this. Correct? I tried getting some help with a macro that would do this but I haven't had any but I think that this would solve the problem. I would like to use a combo box that is populated by all the matches in column B and than have other cell in the same row bet filled with the price, cost and time to install. Any suggestions? I know hire a professional!!!! Marc:) -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have to work books one with pricing and the other the actual
If I'm not mistaken VLOOKUP only works for the far right column is there a
function that would work the same way for columns B and C? The price book as it stands right now hav over 1100 rows of items. Marc "paul" wrote in message ... yes,you can use vlookup to make "dependent" lists for combobox drop downs,and vlookup again to populate cost time etc..... You just end up with lots of vlookup formula back to your price tables,which presumably already have anyway. If your vlookup formulas have extra space in them when you create them(ie your ranges are bigger than the existing tables you have room to add rows without having to adjust formulas -- paul remove nospam for email addy! "Marc" wrote: Can I use VLOKUP to match a list of items to populate a combo box? Say "Speakers in-ceiling". If so than I can use "Match" if the item is chosen to populate the cost, price and time to install cells. Marc "Dave Peterson" wrote in message ... I think I would use/add a key column that I can use to match up with the price book. For instance, if the user chooses a nice unique item/description/part number in the Quote workbook, you could use formulas to retrieve the data that you want. In general, it'll look something like: =IF(A1="","",IF(ISERROR(VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE)),"Missin g", VLOOKUP(A1,[book2.xls]Sheet1!$A:$F,2,FALSE))) (that's with the "sending" workbook open.) If you close that other workbook, excel will adjust the formula to include the paths: =IF(A1="","",IF(ISERROR( VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE)), "Missing", VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$F,2,FALSE))) You may want to read Debra Dalgleish's notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) Marc wrote: In some cases the prices are linked from the price book to the quote book. If I make changes added lines to the price book and the quote book is not opened the the quote books will not update correctly. Correct? If both books are open I can get around this. Correct? I tried getting some help with a macro that would do this but I haven't had any but I think that this would solve the problem. I would like to use a combo box that is populated by all the matches in column B and than have other cell in the same row bet filled with the price, cost and time to install. Any suggestions? I know hire a professional!!!! Marc:) -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
