Same "NAME" for different criteria possible?

[mohan.shocks]

While creating drop down list in data validation, excel 2007 accepts unique NAME only. I have to use same NAME for different criteria, kindly help me to solve this problem.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

A unique name can only refer to one expression at a time; however if you make that expression a formula, the result of that formula could provide different criteria at different times.

One example is Dependent Data Validation. The Contextures site has instructions.
http://www.contextures.com/xldataval02.html

If you are wanting something else please provide more information and possibly an example of how you would use this.

 

[mohan.shocks]

Hi and Welcome to the Board,

A unique name can only refer to one expression at a time; however if you make that expression a formula, the result of that formula could provide different criteria at different times.

One example is Dependent Data Validation. The Contextures site has instructions.
http://www.contextures.com/xldataval02.html

If you are wanting something else please provide more information and possibly an example of how you would use this.

First thanks for the information, but my problem is for example creating a data validation dependent drop down list for CONTINENT>COUNTRY>REGION>CITY>LOCATION.

Assume that in the above criteria, if there is place having same NAME in all of the criteria, then how to assign the same NAME to all? excel accepts unique Names only.
Assume that one of the
Continent Name = ROSE
Country Name = ROSE
Region Name = ROSE
City Name = ROSE
If the "ROSE" is a common NAME in all the criteria, then assigning this NAME not accepted in excel.
I tried adding a number to the NAME, (ROSE1,ROSE2,ROSE3....) it works but the name display in the criteria is also as ROSE1,ROSE2...!
There is any solution to solve this issue, or any method to display the NAME in the criteria without added numbers.
Help me.
Mohan.

 

[Jerry Sullivan]

Hi mohan,

Your explanation helped clarify your original question- thanks.

Are you using the same approach and formulas as the Contextures Example?

If so, you could try modifying your defined names to be unique for each level for all lists. For example lists at Continent Level are:
Europe1
Asia1
Rose1

The items under the list wouldn't have the identifier. So Europe1 range list items are:
Germany
Rose
France

Then modify the INDIRECT formulas in your Validation Criteria to read:
=INDIRECT(A2) & "1"
=INDIRECT(B2) & "2"
=INDIRECT(C2) & "3"
=INDIRECT(D2) & "4"

So to be clear, the Defined Names end in 1,2,3,4 but the Items in the Lists don't end in numbers.

 

[mohan.shocks]

Hi Jerry,
Your guidance helped me to solve the above issue,thanks.

your method of assigning not worked for me, because the NAME repeats in several criteria is "EGT". (short form of East George Town). If assigning EGT1 excel accepts as cell reference not as NAME range.

Then I followed the method in contexture link as you posted in your previous reply, helped me to solve the problem.

Modified Data validation criteria formula:

=INDIRECT(SUBSTITUTE(B2&C2," ","_"))

- Mohan.

 

[Jerry Sullivan]

Hi Jerry,
Your guidance helped me to solve the above issue,thanks.

your method of assigning not worked for me, because the NAME repeats in several criteria is "EGT". (short form of East George Town). If assigning EGT1 excel accepts as cell reference not as NAME range.

Then I followed the method in contexture link as you posted in your previous reply, helped me to solve the problem.

Modified Data validation criteria formula:

=INDIRECT(SUBSTITUTE(B2&C2," ","_"))

- Mohan.

Hi Mohan,

That's a good point. When we were using ROSE as an example, I hadn't considered that you might have some Names that would conflict with range addresses.

Another work around besides the one you came up with would be to have unique names for each list that don't conflict with range addresses.
EGT_1
EGT_2
EGT_3
EGT_4

Then your INDIRECT formulas in your Validation Criteria could read:
=INDIRECT(A2) & "_1"
=INDIRECT(B2) & "_2"
=INDIRECT(C2) & "_3"
=INDIRECT(D2) & "_4"

Thanks for updating this thread with what you discovered.
It really helps for others who might be trying to solve the same problem.

 

[mikerickson]

Another option could be of this form

=CHOOSE(MATCH(A1,{"Germany","Rose","France"},0),Sheet2!$A$1:$A$10,Sheet2!$B$1:$B$7,Sheet2!$A$11:$A$17)