Articles

Solved Formula in validation

August 1, 2011 at 10:25:46
Specs: Windows XP

Hi Team,

I created Drop down list in Excel 2007.

Country
State
District
Area

If I select country name in drop down list than remaining drop list(states, districts and areas) should show the details belongs that particular country.

Formula which I used
States(working) =IF(LEN(A1)>0,INDIRECT($A$1),Allstates)
District(not working) =IF(AND(LEN(A1)="India",LEN(A2)<0),Indiadis,IF(LEN(A2)>0,INDIRECT($A$2),Alldistricts))

Allstates, Alldistricts and Indiadis are cell ranges

Please Help to resolve this issue.


See More: Formula in validation

Report •


✔ Best Answer
August 2, 2011 at 16:00:05

I am going to provide the steps for one country. If you have more than one country, you merely have to expand this concept for each one.

That will means you will have to go one more level deeper with your Named Ranges and Data Validation…List sources.

Please read this carefully and all the way through. It might not make sense until you have read it all the way through and possibly even tried it.

The concept is to break down the list of Districts and Areas into smaller groups using Named Ranges. The Concatenate operator (&) is then used to build a string that the INDIRECT function can use to retrieve a specific Named Range based on what is in the cells that contain the States and Districts Drop Downs.

The following Named Ranges and cell contents are based on the table below.

Note: DD stands for DropDown

A1 contains a Country, in my example India.
A2 contains the DD for the States
A3 contains the DD for the Districts
A4 contains the DD for the Areas

B1:B4 contain the names of 4 States in India

C1:C8 contain the names of Districts. In my example, there are 2 Districts in each State. (I do not know the names of any real Districts, so I made up those names as examples.)

D1:D16 contain the names of the 16 Areas in India. (I do not know the names of any real Areas, so I made up those generic names as examples.)

D1:D4 are Areas in Tamilnadu, D5:D8 are Areas in Haryana, etc. In other words, there are 4 Areas per State.

D1:D2 are Areas in TamDistrict1, D3:D4 are Areas in TamDistrict2, D5:D6 are Areas in HarDistrict1, etc. In other words, there are 2 Areas per District.

The Named Ranges

B1:B4 is Named States

C1:C2 is Named Tamilnadu
C3:C4 is Named Haryana
etc.

D1:D16 in Named Areas to include all Areas

D1:D4 is Named TamilnaduAreas
D5:D8 is Named HaryanaAreas
etc. (This breaks down the 16 Areas into 4 Areas per State)

D1:D2 is Named TimilnaduTamDistrict1Areas
D3:D4 is Named TimilnaduTamDistrict2Areas
D5:D6 is Named HaryanaHarDistrict1Areas
D7:D8 is Named HaryanaHarDistrict2Areas
etc. (This breaks down the Areas into 2 Areas per District)


The Drop Downs

A2 Data Validation … List … =States
The 4 States in B1:B4 will be listed

A3 Data Validation … List … =INDIRECT(A2)
The 2 Districts in the State chosen in A2 will be listed

A4 Data Validation … List … =INDIRECT(A2 & A3 & “Areas”)
The list of Areas that will appear will be based on the contents of A2 and A3, with the string “Areas” Concatenated on the end.

When A2 and A3 (States & Districts) are empty, =INDIRECT(A2 & A3 & “Areas”) will return Areas and the Named Range “Areas” (D1:D16) will be listed.

When A2 (States) is populated but A3 (Districts) is empty, =INDIRECT(A2 & A3 & “Areas”) will return A2 & ”Areas” e.g TamilnaduAreas and D1:D4 will be listed.

When A2 (States) and A3 (Districts) are both populated, =INDIRECT(A2 & A3 & “Areas”) will return e.g. TimilnaduTamDistrict2Areas and only D1:D2 will be listed.

I hope I have explained that clearly enough. If not, come and back and I'll try to explain it differently.


     A                B              C          D    
1 India           Tamilnadu     TamDistrict1       Area1
2 DD States       Haryana       TamDistrict2       Area2
3 DD Districts    Bihar         HarDistrict1       Area3
4 DD Areas        Goa           HarDistrict2       Area4
5                               BihDistrict1       Area5
6                               BihDistrict2       Area6
7                               GoaDistrict1       Area7
8                               GoaDistrict2       Area8
9                                                  Area9
10                                                 Area10
11                                                 Area11
12                                                 Area12
13                                                 Area13
14                                                 Area14
15                                                 Area15
16                                                 Area16

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 1, 2011 at 10:47:43

Please see my response to your previous question.

If I understand your requirements correctly, you are making this more difficult than it needs to be.

By being more creative with your Named Ranges, your Data Validation formulas can be much less cumbersome.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 2, 2011 at 03:18:03


Thank you,

But still my problem not resolved. I chaked with the web adress which provided.

I created drop down list as below:

Country:
State:
Districts:
Areas:

I want to insert many conditons in last area cell, and the conditons are below:

* If I clear all above drop list, Area cell should display Allareas belogs to all country
* If I select only India country, Area cell should display in list Indiaareas
* If I select India country and Tamilnadu state, Area cell should have details list for Indiatamilnaduareas
* If I select India country, Tamilnadu state and Dharmapuri districts, Area cell should have detail list for Indiatamilnadudharmapuriareas

I am unable to use "IF & AND" function together into validation list.

So please help me to solve this issue.


Report •

#3
August 2, 2011 at 04:05:07

Please stop posting your question multiple times.

You have started 3 threads all related to this same question. Going forward, please use this thread only to update your question.

I have deleted your most recent thread.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 2, 2011 at 04:15:25

Sorry,

Can i get the answer for above question?


Report •

#5
August 2, 2011 at 09:28:36

You should get an answer as time allows.

Since we are volunteers here and most of us have "real jobs" we answer these questions in our spare time.

In order to fully explain the steps involved, I'm going to have to set up a worksheet, test a few things and then post the step by step instructions.

Be patient.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 2, 2011 at 16:00:05
✔ Best Answer

I am going to provide the steps for one country. If you have more than one country, you merely have to expand this concept for each one.

That will means you will have to go one more level deeper with your Named Ranges and Data Validation…List sources.

Please read this carefully and all the way through. It might not make sense until you have read it all the way through and possibly even tried it.

The concept is to break down the list of Districts and Areas into smaller groups using Named Ranges. The Concatenate operator (&) is then used to build a string that the INDIRECT function can use to retrieve a specific Named Range based on what is in the cells that contain the States and Districts Drop Downs.

The following Named Ranges and cell contents are based on the table below.

Note: DD stands for DropDown

A1 contains a Country, in my example India.
A2 contains the DD for the States
A3 contains the DD for the Districts
A4 contains the DD for the Areas

B1:B4 contain the names of 4 States in India

C1:C8 contain the names of Districts. In my example, there are 2 Districts in each State. (I do not know the names of any real Districts, so I made up those names as examples.)

D1:D16 contain the names of the 16 Areas in India. (I do not know the names of any real Areas, so I made up those generic names as examples.)

D1:D4 are Areas in Tamilnadu, D5:D8 are Areas in Haryana, etc. In other words, there are 4 Areas per State.

D1:D2 are Areas in TamDistrict1, D3:D4 are Areas in TamDistrict2, D5:D6 are Areas in HarDistrict1, etc. In other words, there are 2 Areas per District.

The Named Ranges

B1:B4 is Named States

C1:C2 is Named Tamilnadu
C3:C4 is Named Haryana
etc.

D1:D16 in Named Areas to include all Areas

D1:D4 is Named TamilnaduAreas
D5:D8 is Named HaryanaAreas
etc. (This breaks down the 16 Areas into 4 Areas per State)

D1:D2 is Named TimilnaduTamDistrict1Areas
D3:D4 is Named TimilnaduTamDistrict2Areas
D5:D6 is Named HaryanaHarDistrict1Areas
D7:D8 is Named HaryanaHarDistrict2Areas
etc. (This breaks down the Areas into 2 Areas per District)


The Drop Downs

A2 Data Validation … List … =States
The 4 States in B1:B4 will be listed

A3 Data Validation … List … =INDIRECT(A2)
The 2 Districts in the State chosen in A2 will be listed

A4 Data Validation … List … =INDIRECT(A2 & A3 & “Areas”)
The list of Areas that will appear will be based on the contents of A2 and A3, with the string “Areas” Concatenated on the end.

When A2 and A3 (States & Districts) are empty, =INDIRECT(A2 & A3 & “Areas”) will return Areas and the Named Range “Areas” (D1:D16) will be listed.

When A2 (States) is populated but A3 (Districts) is empty, =INDIRECT(A2 & A3 & “Areas”) will return A2 & ”Areas” e.g TamilnaduAreas and D1:D4 will be listed.

When A2 (States) and A3 (Districts) are both populated, =INDIRECT(A2 & A3 & “Areas”) will return e.g. TimilnaduTamDistrict2Areas and only D1:D2 will be listed.

I hope I have explained that clearly enough. If not, come and back and I'll try to explain it differently.


     A                B              C          D    
1 India           Tamilnadu     TamDistrict1       Area1
2 DD States       Haryana       TamDistrict2       Area2
3 DD Districts    Bihar         HarDistrict1       Area3
4 DD Areas        Goa           HarDistrict2       Area4
5                               BihDistrict1       Area5
6                               BihDistrict2       Area6
7                               GoaDistrict1       Area7
8                               GoaDistrict2       Area8
9                                                  Area9
10                                                 Area10
11                                                 Area11
12                                                 Area12
13                                                 Area13
14                                                 Area14
15                                                 Area15
16                                                 Area16

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question