Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pieman
 
Posts: n/a
Default Dropdown list connected to another workbook

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron de Bruin
 
Posts: n/a
Default Dropdown list connected to another workbook

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

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

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


"Pieman" wrote in message ...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pieman
 
Posts: n/a
Default Dropdown list connected to another workbook

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

"Ron de Bruin" wrote:

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

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

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


"Pieman" wrote in message ...
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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Debra Dalgleish
 
Posts: n/a
Default Dropdown list connected to another workbook

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 InsertNameDefine
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:
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

"Ron de Bruin" wrote:


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

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

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


"Pieman" wrote in message ...

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






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pieman
 
Posts: n/a
Default Dropdown list connected to another workbook

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

"Debra Dalgleish" wrote:

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 InsertNameDefine
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:
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

"Ron de Bruin" wrote:


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

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

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


"Pieman" wrote in message ...

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





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Debra Dalgleish
 
Posts: n/a
Default Dropdown list connected to another workbook

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

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

"Debra Dalgleish" wrote:


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 InsertNameDefine
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:

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

"Ron de Bruin" wrote:



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

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

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


"Pieman" wrote in message ...


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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I link one cell to a list of info in another workbook? PaulGrowns1 Excel Discussion (Misc queries) 3 November 24th 05 10:02 PM
Dropdown list with named cells WaterDog Excel Worksheet Functions 1 November 18th 05 04:08 AM
How do I publish a workbook with a list? ryan Excel Discussion (Misc queries) 0 October 21st 05 03:34 AM
Dropdown list question pennyrod Excel Discussion (Misc queries) 2 October 18th 05 09:55 PM
How to show dropdown list from another workbook without running it Kuche Excel Discussion (Misc queries) 1 June 29th 05 07:05 PM


All times are GMT +1. The time now is 11:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"