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

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."

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!
==============================================


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

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



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