Drop down list dependent on contents on a cell located in another workbook

[IggyK]

Hello,

I've been googling extensively to try and find a solution to my problem without success. Perhaps someone here can help me.

I have a worksheet in Workbook1 that I would like to create a drop down list dependent on specific contents located in Workbook2. Workbook2 is referenced by other spreadsheets and its contents and layout cannot be changed.

Workbook2 contents are laid out this way:

Column A Column B
SCHOOL NAME REGION
School 1 Orlando
School 2 Tampa
School 3 Miami
School 4 Miami
School 5 Tampa
School 6 Orlando
School 7 Jacksonville
School 8 Tampa

In Workbook1, I would like to present a 'custom' drop down list to the user a list of schools they can choose from based on a specific region.

For example, the drop down list should only present the school names from a specific region....say, Tampa. Therefore the contents of the drop down list would only include School 2, School 5 and School 8.

Keep in mind, the contents and layout of Workbook2 cannot be changed.

I'm hoping someone here can help me.
BTW- I am an Excel novice.

Thanks! IggyK

 

[MARK858]

Have a read of the 2 links below...

https://www.contextures.com/xlDataVal05.html

https://www.contextures.com/xlDataVal02.html

 

[IggyK]

Thank you. I appreciate your reply.

I previously found the two links you provided before I posed my question.

However I am a bit confused. I understand what the examples in "contextures.com" is doing but I don't understand how I can do this without making any changes to "Workbook 2" in my example. "Workbook 2" is protected and I am unable to change it by creating named ranges or tables.

I need to be able to do this within "Workbook 1" which I have created. I can only read the information as it is in "Workbook 2".

"School Names" is a named range in "Workbook 2" but "Region" is not.

Thanks....IggyK

 

[MARK858]

Use Indirect in the source box in the validation i.e.

=INDIRECT("[Workbook2]Sheet1!$A$2:$A$9")

But obviously it will be a fixed range whereas a named range will adapt.

Btw, I inadvertently posted the 2003 link, below is the 2007+ link.

https://contexturesblog.com/archives/2009/05/12/data-validation-list-from-different-workbook/

Although personally I would just reference the cells in Workbook2 on another sheet in Workbook1 (or pull the info in using a macro more likely in my case) then do the validation based on that.

 

[IggyK]

Again, Thank you for replying. I'm having a difficult time wrapping my head around this.
Let me provide you with a better example of what I'm looking for-

In a Dropdown in Workbook1, I only want those school choices that contain "ORL" in the "Region" column.
In the example below- School 1, School 5, and School 6
School Name is a named range- SchoolName



Data in Workbook2-


SCHOOL NAME REGION
School 1 ORL
School 2 SFL
School 3 JAX
School 4 SFL
School 5 ORL
School 6 ORL
School 7 JAX