Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I link one cell to a list of info in another workbook? | Excel Discussion (Misc queries) | |||
Dropdown list with named cells | Excel Worksheet Functions | |||
How do I publish a workbook with a list? | Excel Discussion (Misc queries) | |||
Dropdown list question | Excel Discussion (Misc queries) | |||
How to show dropdown list from another workbook without running it | Excel Discussion (Misc queries) |