Populate list or combo box from closed workbook

[Andrew]

Hello,
I am working on a project where I would like to have a customer
database in one workbook, and an invoice/PO generator in another
workbook. The PO generator will have either a drop down list or a
combo box which is linked to the customer database. The drop down
list would show all the customers and information about the customer
database.

I would like to use a drop down list on the PO sheet, so that a user
can scroll through all possible customers. But I have learned that a
drop down list can only be validated when the reference workbook is
open. And I would prefer that the customer database workbook remain
closed. So, first of all...can this be done with a drop down list?
If so, how? And...if it can't be done with a drop down list, what's
the next choice? I had assumed using a userform with a combo box
which contains a drop down list of customers.

Thanks in advance for your help.

Andy

 

[Don Guillett Excel MVP]

Hello,
I am working on a project where I would like to have a customer
database in one workbook, and an invoice/PO generator in another
workbook.  The PO generator will have either a drop down list or a
combo box which is linked to the customer database.  The drop down
list would show all the customers and information about the customer
database.

I would like to use a drop down list on the PO sheet, so that a user
can scroll through all possible customers.  But I have learned that a
drop down list can only be validated when the reference workbook is
open.  And I would prefer that the customer database workbook remain
closed.  So, first of all...can this be done with a drop down list?
If so, how?  And...if it can't be done with a drop down list, what's
the next choice?  I had assumed using a userform with a combo box
which contains a drop down list of customers.

Thanks in advance for your help.

Andy

One possible way to do this without opening the source wb would be to
have formulas refer to the workbook and an edit/replace macro to
change the formula for the customer ??

 

[Andrew]

One possible way to do this without opening the source wb would be to
have formulas refer to the workbook and an edit/replace macro to
change the formula for the customer ??

I already have a lookup table link for all of the customer
information. So, one cell on the PO wb is the customer name, and then
several other cells (for address, phone, etc.) are based on lookup
tables using the customer name as the search term. The lookup table
works fine through a closed workbook. But the question is how to get
the list of customers onto the PO page in the form of a drop down box?

 

[Don Guillett Excel MVP]

I already have a lookup table link for all of the customer
information.  So, one cell on the PO wb is the customer name, and then
several other cells (for address, phone, etc.) are based on lookup
tables using the customer name as the search term.  The lookup table
works fine through a closed workbook.  But the question is how to get
the list of customers onto the PO page in the form of a drop down box?- Hide quoted text -

- Show quoted text -

"If desired, send your fileS to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Andrew]

"If desired, send your fileS to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Don,
Thank you for your offer. I used an import data link from the
"customer info" workbook to the "PO" workbook. The PO workbook has
one drop down list which is linked to the data imported from the
customer info book. It seems to work well. There is a lot to know
about getting data from one workbook to another.