Data Validation using 2 or more different ranges

[zapppsr]

Hi folks:

I have 2 names for 2 ranges:

Region1 - Range A2:A6 - Content: City1, City2, City3, City4, City5
Region2 - Range C2:C6 - Content: City6, City7, City8, City9, City10

I want to do a data validation, using a list that merges the 2 names (Region1 and Region2)

Normally, I would go to data validation and choose list and press F3 and select a name. Eg.: =Region1.

That works and the list displays Cities from 1-5

But I want to join the 2 names and the list in the field with data validation give me the Cities from 1-10.

Any tips or hints?

Thanks in advance!

 

[T. Valko]

Hi folks:

I have 2 names for 2 ranges:

Region1 - Range A2:A6 - Content: City1, City2, City3, City4, City5
Region2 - Range C2:C6 - Content: City6, City7, City8, City9, City10

I want to do a data validation, using a list that merges the 2 names (Region1 and Region2)

Normally, I would go to data validation and choose list and press F3 and select a name. Eg.: =Region1.

That works and the list displays Cities from 1-5

But I want to join the 2 names and the list in the field with data validation give me the Cities from 1-10.

Any tips or hints?

Thanks in advance!

You'd have to make a 3rd list that combines the other 2 lists and then use this new 3rd list as the source for the drop down.

Or, you can move Region2 to A7:A11 and then use A2:A11 as the source for the drop down.

 

[zapppsr]

Hi:

Moving the data is not an option.

How do I combine the two names in one 3rd?

I tried naming with the 2 ranges selected. It accepted but when I try to use the new name, it does not work.

When I check the name, it shows this:

=Plan1!$A$2:$A$6;Plan1!$C$2:$C$6

The error says data must be in one row or one column.

 

[T. Valko]

Hi:

Moving the data is not an option.

How do I combine the two names in one 3rd?

I tried naming with the 2 ranges selected. It accepted but when I try to use the new name, it does not work.

When I check the name, it shows this:

=Plan1!$A$2:$A$6;Plan1!$C$2:$C$6

The error says data must be in one row or one column.

Book1
	A	B	C	D
2	City1	City6	_	City1
3	City2	City7	_	City2
4	City3	City8	_	City3
5	City4	City9	_	City4
6	City5	City10	_	City5
7	_	_	_	City6
8	_	_	_	City7
9	_	_	_	City8
10	_	_	_	City9
11	_	_	_	City10
Sheet1

Formula enterd in D2 and copied down to D6:

=A2

Formula entered in D7 and copied down to D11:

=B2

 

[zapppsr]

Ok. Thank you.

 

[T. Valko]

Ok. Thank you.

You're welcome!