Validation list on separate workbook

dragon007

New Member
Joined
Aug 6, 2002
Messages
43
Hi everyone,

Is it possible to validate cells in one workbook using a drop-down list where the source data is contained in another workbook. if so, how can i do that?

i was serching the board looking for similar topic, but i found only when it is in the same workbook and difirrent worksheets.

Thanks!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi dragon007:

Yes, you can have the validation list in another workbook, provided ...

1. You use a Named Range for the list (and provide the Workbook, Worksheet, and the List Range information) in the validation dialog box

2. The workbook with the validation list must be open

Regards!

Yogi
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
From the help files:

"A formula for a limit can evaluate data only on the worksheet where you set up the restrictions. To use data in other worksheets or workbooks in a formula, enter a reference to the external data in a cell on the active worksheet, or define a name for the external data on the active worksheet. The formula can then refer to the cell or name on the same worksheet. For example, if the data you want to use in a formula is in cell A6 on the first worksheet in a workbook named Budget.xls, you could define the name ValidData on the active worksheet to be =[Budget.xls]Sheet1!$A$6 and then enter a reference to ValidData when you specify the limits for the data."

Paddy
 

dragon007

New Member
Joined
Aug 6, 2002
Messages
43
thanks Yogi and Paddy for your posts.
i actually want to have a master file with all the data in the server, so that every body shares the same data for the validate list.
but in the server we don't have excel. so it wouldn't be possible to have the file open there.
is there any other solution to share the same data between different validation lists, with the workbook contaning the data closed?

thanks
This message was edited by dragon007 on 2002-10-06 00:21
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,863
Messages
5,834,051
Members
430,259
Latest member
msthiagu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top