Using a named range from one xls in another xls

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
459
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Go to Data|Validation and Choose list

Then in the Source box enter =list, replacing "list" with your named range.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top