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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

=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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
>

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
[email protected]
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >