I have to work books one with pricing and the other the actual quotes.

[Marc]

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]

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]

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

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())

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]

=vlookup() will return a single value to the cell with the formula.

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

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())

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

 

[Guest]

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.

=vlookup() will return a single value to the cell with the formula.

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

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

 

[Guest]

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
(e-mail address removed)
remove nospam for email addy!

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

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())

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

 

[Marc]

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

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
(e-mail address removed)
remove nospam for email addy!

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

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