create a drop down list with the source from a different workbook

[Guest]

Hi,
I tried to create a drop down list (Data > Validation > List) with the
source from a different work book (If you type the list in a different
workbook, define a name with an external reference to the list.). I followed
the instructions given in the Microsoft Online Assistant.
Iam not able to create the same as iam a getting a message stating that "You
may not use references to other worksheets or workbooks for Data validation
criteria."

 

[RagDyeR]

Try Debra Dalgleish's web site on this subject:

http://www.contextures.com/tiptech.html

Scroll down to "D", and see all the pages on "Data Validation".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,
I tried to create a drop down list (Data > Validation > List) with the
source from a different work book (If you type the list in a different
workbook, define a name with an external reference to the list.). I followed
the instructions given in the Microsoft Online Assistant.
Iam not able to create the same as iam a getting a message stating that "You
may not use references to other worksheets or workbooks for Data validation
criteria."

 

[Arvi Laanemets]

Hi


Add a sheet into your workbook, and using links, mirror the list from
another workbook into this sheet. I.e. into cell A1 enter the formula
=IF('DriveLetter:\Path\[AnotherWorkbook.xls]SheetWithList'!A1="","",'Drive:\
Path\[AnotherWorkbook.xls]SheetWithList'!A1)
and copy it so whole list is mirrored.

Define a dynamic named range based on mirrored list (I assume it is in
column A, with header in cell A1), like
MyList=OFFSET(MirrorSheet!$A$2,,,COUNTIF(MirrorSheet!$A:$A,">""")-1,1)

Select the cell/range you want to be formatted as data validation list,
select Data.Validation.List from menu, and nto sourve field enter
=MyList

(replace all worksheet and workbook names etc. with ones used by you, of
course)

Arvi Laanemets