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

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

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

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


"Pieman" <[email protected]> wrote in message news:[email protected]...
> 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

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" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>

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 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" <[email protected]> wrote in message news:[email protected]...
>>
>>>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, 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 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 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" <[email protected]> wrote in message news:[email protected]...
> >>
> >>>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
>
>

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 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 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" <[email protected]> wrote in message news:[email protected]...
>>>>
>>>>
>>>>>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