Dropdown list connected to another workbook

[Pieman]

Hi, does anyone know how to create a dropdown box that will list the contents
of a column in another workbook?

Basically, I have two workbooks. One is a sales register that records the
serial number of a marketing campaign that generated the sale. The serial
numbers are 8 digits long and located in the second workbook which is a
marketing campaign register. So I need the name of all current marketing
campaigns in the marketing register to be displayed in the dropdown list but
the serial number of the selected campaign to be inserted into the cell in
the sales register.

Hope you understand that, but I would appreciate any help.

Thanks
Simon

 

[Ron de Bruin]

See
http://www.contextures.com/xlDataVal01.html

This one
http://www.contextures.com/xlDataVal05.html

 

[Pieman]

Hi, thanks for that reference. I have got the dynamic dropdown list to work
when the source workbook is open, but is there a way to do this when the
source workbook is closed?

I thought the dynamic dropdown box would be updated each time the active
workbook is opened, but I just get an error message if the source workbook is
closed at the same time.

Thanks
Simon

 

[Debra Dalgleish]

To use a list from closed workbook, you could link to the list on a
hidden sheet in the workbook that contains the data validation dropdown.
For example, if Dropdown.xls contains the cells with the dropdown lists,
and List.xls contains the master list --

In Dropdown.xls, on Sheet2, cell A1, link to the master list:
=[List.xls]Sheet1!A1
Copy the formula down as far as required to show all the entries in the
master list, and extra rows for future entries.
To create a dynamic range based on this list, choose Insert>Name>Define
Type a name, e.g. MyList
In the Refers to box, type:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-COUNTIF(Sheet2!$A:$A,0),1)
For the data validation cells, allow a List, and use Mylist as the source.
You can hide the list that contains the linked list.
When you open the Dropdown.xls workbook, update the links.

 

[Pieman]

Debra, thats fantastic. It works like a dream, thank you.
Simon

To use a list from closed workbook, you could link to the list on a
hidden sheet in the workbook that contains the data validation dropdown.
For example, if Dropdown.xls contains the cells with the dropdown lists,
and List.xls contains the master list --

In Dropdown.xls, on Sheet2, cell A1, link to the master list:
=[List.xls]Sheet1!A1
Copy the formula down as far as required to show all the entries in the
master list, and extra rows for future entries.
To create a dynamic range based on this list, choose Insert>Name>Define
Type a name, e.g. MyList
In the Refers to box, type:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-COUNTIF(Sheet2!$A:$A,0),1)
For the data validation cells, allow a List, and use Mylist as the source.
You can hide the list that contains the linked list.
When you open the Dropdown.xls workbook, update the links.

Hi, thanks for that reference. I have got the dynamic dropdown list to work
when the source workbook is open, but is there a way to do this when the
source workbook is closed?

I thought the dynamic dropdown box would be updated each time the active
workbook is opened, but I just get an error message if the source workbook is
closed at the same time.

Thanks
Simon

:

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.

Debra, thats fantastic. It works like a dream, thank you.
Simon

:

To use a list from closed workbook, you could link to the list on a
hidden sheet in the workbook that contains the data validation dropdown.
For example, if Dropdown.xls contains the cells with the dropdown lists,
and List.xls contains the master list --

In Dropdown.xls, on Sheet2, cell A1, link to the master list:
=[List.xls]Sheet1!A1
Copy the formula down as far as required to show all the entries in the
master list, and extra rows for future entries.
To create a dynamic range based on this list, choose Insert>Name>Define
Type a name, e.g. MyList
In the Refers to box, type:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-COUNTIF(Sheet2!$A:$A,0),1)
For the data validation cells, allow a List, and use Mylist as the source.
You can hide the list that contains the linked list.
When you open the Dropdown.xls workbook, update the links.

Hi, thanks for that reference. I have got the dynamic dropdown list to work
when the source workbook is open, but is there a way to do this when the
source workbook is closed?

I thought the dynamic dropdown box would be updated each time the active
workbook is opened, but I just get an error message if the source workbook is
closed at the same time.

Thanks
Simon

:



See
http://www.contextures.com/xlDataVal01.html

This one
http://www.contextures.com/xlDataVal05.html

--
Regards Ron de Bruin
http://www.rondebruin.nl




Hi, does anyone know how to create a dropdown box that will list the contents
of a column in another workbook?

Basically, I have two workbooks. One is a sales register that records the
serial number of a marketing campaign that generated the sale. The serial
numbers are 8 digits long and located in the second workbook which is a
marketing campaign register. So I need the name of all current marketing
campaigns in the marketing register to be displayed in the dropdown list but
the serial number of the selected campaign to be inserted into the cell in
the sales register.

Hope you understand that, but I would appreciate any help.

Thanks
Simon