# Solved Formula in validation

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

 Hi Team,I created Drop down list in Excel 2007.CountryStateDistrictAreaIf 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 usedStates(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

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 DropDownA1 contains a Country, in my example India.A2 contains the DD for the StatesA3 contains the DD for the DistrictsA4 contains the DD for the AreasB1:B4 contain the names of 4 States in IndiaC1: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 RangesB1:B4 is Named StatesC1:C2 is Named TamilnaduC3:C4 is Named Haryanaetc.D1:D16 in Named Areas to include all AreasD1:D4 is Named TamilnaduAreasD5:D8 is Named HaryanaAreasetc. (This breaks down the 16 Areas into 4 Areas per State)D1:D2 is Named TimilnaduTamDistrict1AreasD3:D4 is Named TimilnaduTamDistrict2AreasD5:D6 is Named HaryanaHarDistrict1AreasD7:D8 is Named HaryanaHarDistrict2Areasetc. (This breaks down the Areas into 2 Areas per District)The Drop DownsA2 Data Validation … List … =StatesThe 4 States in B1:B4 will be listedA3 Data Validation … List … =INDIRECT(A2)The 2 Districts in the State chosen in A2 will be listedA4 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 IndiatamilnadudharmapuriareasI 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

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
 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 DropDownA1 contains a Country, in my example India.A2 contains the DD for the StatesA3 contains the DD for the DistrictsA4 contains the DD for the AreasB1:B4 contain the names of 4 States in IndiaC1: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 RangesB1:B4 is Named StatesC1:C2 is Named TamilnaduC3:C4 is Named Haryanaetc.D1:D16 in Named Areas to include all AreasD1:D4 is Named TamilnaduAreasD5:D8 is Named HaryanaAreasetc. (This breaks down the 16 Areas into 4 Areas per State)D1:D2 is Named TimilnaduTamDistrict1AreasD3:D4 is Named TimilnaduTamDistrict2AreasD5:D6 is Named HaryanaHarDistrict1AreasD7:D8 is Named HaryanaHarDistrict2Areasetc. (This breaks down the Areas into 2 Areas per District)The Drop DownsA2 Data Validation … List … =StatesThe 4 States in B1:B4 will be listedA3 Data Validation … List … =INDIRECT(A2)The 2 Districts in the State chosen in A2 will be listedA4 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.