software

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

See
www.contextures.com/xlDataVal01.html

This one
www.contextures.com/xlDataVal05.html

--
Regards Ron de Bruin
www.rondebruin.nlquot;Piemanquot; gt; wrote in message ...
gt; Hi, does anyone know how to create a dropdown box that will list the contents
gt; of a column in another workbook?
gt;
gt; Basically, I have two workbooks. One is a sales register that records the
gt; serial number of a marketing campaign that generated the sale. The serial
gt; numbers are 8 digits long and located in the second workbook which is a
gt; marketing campaign register. So I need the name of all current marketing
gt; campaigns in the marketing register to be displayed in the dropdown list but
gt; the serial number of the selected campaign to be inserted into the cell in
gt; the sales register.
gt;
gt; Hope you understand that, but I would appreciate any help.
gt;
gt; Thanks
gt; Simon
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

quot;Ron de Bruinquot; wrote:

gt; See
gt; www.contextures.com/xlDataVal01.html
gt;
gt; This one
gt; www.contextures.com/xlDataVal05.html
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;Piemanquot; gt; wrote in message ...
gt; gt; Hi, does anyone know how to create a dropdown box that will list the contents
gt; gt; of a column in another workbook?
gt; gt;
gt; gt; Basically, I have two workbooks. One is a sales register that records the
gt; gt; serial number of a marketing campaign that generated the sale. The serial
gt; gt; numbers are 8 digits long and located in the second workbook which is a
gt; gt; marketing campaign register. So I need the name of all current marketing
gt; gt; campaigns in the marketing register to be displayed in the dropdown list but
gt; gt; the serial number of the selected campaign to be inserted into the cell in
gt; gt; the sales register.
gt; gt;
gt; gt; Hope you understand that, but I would appreciate any help.
gt; gt;
gt; gt; Thanks
gt; gt; Simon
gt;
gt;
gt;

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 Insertgt;Namegt;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 wrote:
gt; Hi, thanks for that reference. I have got the dynamic dropdown list to work
gt; when the source workbook is open, but is there a way to do this when the
gt; source workbook is closed?
gt;
gt; I thought the dynamic dropdown box would be updated each time the active
gt; workbook is opened, but I just get an error message if the source workbook is
gt; closed at the same time.
gt;
gt; Thanks
gt; Simon
gt;
gt; quot;Ron de Bruinquot; wrote:
gt;
gt;
gt;gt;See
gt;gt;www.contextures.com/xlDataVal01.html
gt;gt;
gt;gt;This one
gt;gt;www.contextures.com/xlDataVal05.html
gt;gt;
gt;gt;--
gt;gt;Regards Ron de Bruin
gt;gt;www.rondebruin.nl
gt;gt;
gt;gt;
gt;gt;quot;Piemanquot; gt; wrote in message ...
gt;gt;
gt;gt;gt;Hi, does anyone know how to create a dropdown box that will list the contents
gt;gt;gt;of a column in another workbook?
gt;gt;gt;
gt;gt;gt;Basically, I have two workbooks. One is a sales register that records the
gt;gt;gt;serial number of a marketing campaign that generated the sale. The serial
gt;gt;gt;numbers are 8 digits long and located in the second workbook which is a
gt;gt;gt;marketing campaign register. So I need the name of all current marketing
gt;gt;gt;campaigns in the marketing register to be displayed in the dropdown list but
gt;gt;gt;the serial number of the selected campaign to be inserted into the cell in
gt;gt;gt;the sales register.
gt;gt;gt;
gt;gt;gt;Hope you understand that, but I would appreciate any help.
gt;gt;gt;
gt;gt;gt;Thanks
gt;gt;gt;Simon
gt;gt;
gt;gt;
gt;gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlDebra, thats fantastic. It works like a dream, thank you.
Simon

quot;Debra Dalgleishquot; wrote:

gt; To use a list from closed workbook, you could link to the list on a
gt; hidden sheet in the workbook that contains the data validation dropdown.
gt; For example, if Dropdown.xls contains the cells with the dropdown lists,
gt; and List.xls contains the master list --
gt;
gt; In Dropdown.xls, on Sheet2, cell A1, link to the master list:
gt; =[List.xls]Sheet1!A1
gt; Copy the formula down as far as required to show all the entries in the
gt; master list, and extra rows for future entries.
gt; To create a dynamic range based on this list, choose Insertgt;Namegt;Define
gt; Type a name, e.g. MyList
gt; In the Refers to box, type:
gt; =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-COUNTIF(Sheet2!$A:$A,0),1)
gt; For the data validation cells, allow a List, and use Mylist as the source.
gt; You can hide the list that contains the linked list.
gt; When you open the Dropdown.xls workbook, update the links.
gt;
gt; Pieman wrote:
gt; gt; Hi, thanks for that reference. I have got the dynamic dropdown list to work
gt; gt; when the source workbook is open, but is there a way to do this when the
gt; gt; source workbook is closed?
gt; gt;
gt; gt; I thought the dynamic dropdown box would be updated each time the active
gt; gt; workbook is opened, but I just get an error message if the source workbook is
gt; gt; closed at the same time.
gt; gt;
gt; gt; Thanks
gt; gt; Simon
gt; gt;
gt; gt; quot;Ron de Bruinquot; wrote:
gt; gt;
gt; gt;
gt; gt;gt;See
gt; gt;gt;www.contextures.com/xlDataVal01.html
gt; gt;gt;
gt; gt;gt;This one
gt; gt;gt;www.contextures.com/xlDataVal05.html
gt; gt;gt;
gt; gt;gt;--
gt; gt;gt;Regards Ron de Bruin
gt; gt;gt;www.rondebruin.nl
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;quot;Piemanquot; gt; wrote in message ...
gt; gt;gt;
gt; gt;gt;gt;Hi, does anyone know how to create a dropdown box that will list the contents
gt; gt;gt;gt;of a column in another workbook?
gt; gt;gt;gt;
gt; gt;gt;gt;Basically, I have two workbooks. One is a sales register that records the
gt; gt;gt;gt;serial number of a marketing campaign that generated the sale. The serial
gt; gt;gt;gt;numbers are 8 digits long and located in the second workbook which is a
gt; gt;gt;gt;marketing campaign register. So I need the name of all current marketing
gt; gt;gt;gt;campaigns in the marketing register to be displayed in the dropdown list but
gt; gt;gt;gt;the serial number of the selected campaign to be inserted into the cell in
gt; gt;gt;gt;the sales register.
gt; gt;gt;gt;
gt; gt;gt;gt;Hope you understand that, but I would appreciate any help.
gt; gt;gt;gt;
gt; gt;gt;gt;Thanks
gt; gt;gt;gt;Simon
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
gt;

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

Pieman wrote:
gt; Debra, thats fantastic. It works like a dream, thank you.
gt; Simon
gt;
gt; quot;Debra Dalgleishquot; wrote:
gt;
gt;
gt;gt;To use a list from closed workbook, you could link to the list on a
gt;gt;hidden sheet in the workbook that contains the data validation dropdown.
gt;gt;For example, if Dropdown.xls contains the cells with the dropdown lists,
gt;gt;and List.xls contains the master list --
gt;gt;
gt;gt;In Dropdown.xls, on Sheet2, cell A1, link to the master list:
gt;gt; =[List.xls]Sheet1!A1
gt;gt;Copy the formula down as far as required to show all the entries in the
gt;gt;master list, and extra rows for future entries.
gt;gt;To create a dynamic range based on this list, choose Insertgt;Namegt;Define
gt;gt;Type a name, e.g. MyList
gt;gt;In the Refers to box, type:
gt;gt; =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-COUNTIF(Sheet2!$A:$A,0),1)
gt;gt;For the data validation cells, allow a List, and use Mylist as the source.
gt;gt;You can hide the list that contains the linked list.
gt;gt;When you open the Dropdown.xls workbook, update the links.
gt;gt;
gt;gt;Pieman wrote:
gt;gt;
gt;gt;gt;Hi, thanks for that reference. I have got the dynamic dropdown list to work
gt;gt;gt;when the source workbook is open, but is there a way to do this when the
gt;gt;gt;source workbook is closed?
gt;gt;gt;
gt;gt;gt;I thought the dynamic dropdown box would be updated each time the active
gt;gt;gt;workbook is opened, but I just get an error message if the source workbook is
gt;gt;gt;closed at the same time.
gt;gt;gt;
gt;gt;gt;Thanks
gt;gt;gt;Simon
gt;gt;gt;
gt;gt;gt;quot;Ron de Bruinquot; wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;See
gt;gt;gt;gt;www.contextures.com/xlDataVal01.html
gt;gt;gt;gt;
gt;gt;gt;gt;This one
gt;gt;gt;gt;www.contextures.com/xlDataVal05.html
gt;gt;gt;gt;
gt;gt;gt;gt;--
gt;gt;gt;gt;Regards Ron de Bruin
gt;gt;gt;gt;www.rondebruin.nl
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;quot;Piemanquot; gt; wrote in message ...
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;gt;Hi, does anyone know how to create a dropdown box that will list the contents
gt;gt;gt;gt;gt;of a column in another workbook?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Basically, I have two workbooks. One is a sales register that records the
gt;gt;gt;gt;gt;serial number of a marketing campaign that generated the sale. The serial
gt;gt;gt;gt;gt;numbers are 8 digits long and located in the second workbook which is a
gt;gt;gt;gt;gt;marketing campaign register. So I need the name of all current marketing
gt;gt;gt;gt;gt;campaigns in the marketing register to be displayed in the dropdown list but
gt;gt;gt;gt;gt;the serial number of the selected campaign to be inserted into the cell in
gt;gt;gt;gt;gt;the sales register.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Hope you understand that, but I would appreciate any help.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Thanks
gt;gt;gt;gt;gt;Simon
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;
gt;gt;--
gt;gt;Debra Dalgleish
gt;gt;Excel FAQ, Tips amp; Book List
gt;gt;www.contextures.com/tiptech.html
gt;gt;
gt;gt;
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html