Using a named range from one xls in another xls

[zendog1960]

I have two spreadsheets. One is called employees and the other is a form. What I want to do is use the named range in 'Employees' as list validation in the form.

How would I go about doing that?

Thanks

 

[NBVC]

Go to Data|Validation and Choose list

Then in the Source box enter =list, replacing "list" with your named range.

 

[zendog1960]

Doing that is not a problem. What I meant to say was that I have two entirely seperate files in different locations on my server. I need to use a named range from one file to a completely different one.

File 1: Employees.xls
File 2: Crew Schedule

I want A6 in Crew Schedule.xls to be a pulldown list populated by the list of employees in Employees.xls.

How would I do that?

 

[zendog1960]

any ideas folks?

 

[Mark O'Brien]

OK, using the Data validation tip from NBVC, the one additional step that you will need to take is this:

In the Crew Schedule workbook create a named range that refers to the named range in the Employees.xls. Then make your data validation refer to the named range in Crew Schedule.

The only drawback with this approach is that the Employees.xls file will need to be open for this to work.

 

[zendog1960]

ok..that won't work then. How can I have it so it doesn't need to be open.? Or at least open it long enough to extract the data then close it. Maybe it can populate a list in the Crew Schedule file in a data type of tab and use the list from there.

The main goal here is to not have all our eggs in one basket (file).

I am certainly open to suggestions on how to make something like this work, even if it means using VBA to solve this.

Thanks all for any help you can provide!