Advanced Validation Required

[csanjeev99]

In A1 cell It will show only Office.

In C2 cell It will show State.

In E1 cell it will show Division.

In G1 cell it will show District.


Now I will explain relation between office, state, division and district.

One Office can have more than 1 State
One State can have more than 1 Division
One Division can have more than 1 District

[TABLE="width: 921"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]for more details i have given below example of data [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office[/TD]
[TD]Office Code[/TD]
[TD]State[/TD]
[TD]State Code[/TD]
[TD]Division[/TD]
[TD]Division Code[/TD]
[TD]District[/TD]
[TD]District Code[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Dadra And Nagar Haveli[/TD]
[TD] [/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Dadra And Nagar Haveli[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Daman And Diu[/TD]
[TD] [/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Daman[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Daman And Diu[/TD]
[TD] [/TD]
[TD]Junagarh[/TD]
[TD] [/TD]
[TD]Diu[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Ahmedabad[/TD]
[TD]055[/TD]
[TD]Ahmadabad[/TD]
[TD]474[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Ahmadabad[/TD]
[TD]055[/TD]
[TD]Bhavnagar[/TD]
[TD]481[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Ahmedabad[/TD]
[TD]055[/TD]
[TD]Gandhinagar[/TD]
[TD]473[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Ahmedabad[/TD]
[TD]055[/TD]
[TD]Kheda[/TD]
[TD]483[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Ahmedabad[/TD]
[TD]055[/TD]
[TD]Surendranagar[/TD]
[TD]475[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Patan[/TD]
[TD]054[/TD]
[TD]Banaskantha[/TD]
[TD]469[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Rajkot[/TD]
[TD]056[/TD]
[TD]Kachchh[/TD]
[TD]468[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Patan[/TD]
[TD]054[/TD]
[TD]Mahesana[/TD]
[TD]471[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Patan[/TD]
[TD]054[/TD]
[TD]Patan[/TD]
[TD]470[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Patan[/TD]
[TD]054[/TD]
[TD]Sabarkantha[/TD]
[TD]472[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Rajkot[/TD]
[TD]056[/TD]
[TD]Amreli[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Rajkot[/TD]
[TD]056[/TD]
[TD]Jamnagar[/TD]
[TD]477[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Rajkot[/TD]
[TD]056[/TD]
[TD]Junagadh[/TD]
[TD]479[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Rajkot[/TD]
[TD]056[/TD]
[TD]Porbandar[/TD]
[TD]478[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Rajkot[/TD]
[TD]056[/TD]
[TD]Rajkot[/TD]
[TD]476[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Bharuch[/TD]
[TD]488[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Navsari[/TD]
[TD]490[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Surat[/TD]
[TD]492[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Tapi[/TD]
[TD]493[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]The Dangs[/TD]
[TD]489[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Surat[/TD]
[TD]058[/TD]
[TD]Valsad[/TD]
[TD]491[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Ahmedabad[/TD]
[TD]055[/TD]
[TD]Anand[/TD]
[TD]482[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Vadodara[/TD]
[TD]057[/TD]
[TD]Dohad[/TD]
[TD]485[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Vadodara[/TD]
[TD]057[/TD]
[TD]Narmada[/TD]
[TD]487[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Vadodara[/TD]
[TD]057[/TD]
[TD]Panchmahal[/TD]
[TD]484[/TD]
[/TR]
[TR]
[TD]Ahmedabad[/TD]
[TD]01[/TD]
[TD]Gujrat[/TD]
[TD]24[/TD]
[TD]Vadodara[/TD]
[TD]057[/TD]
[TD]Vadodara[/TD]
[TD]486[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Bangalore Rural[/TD]
[TD]583[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Bangalore Urban[/TD]
[TD]572[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Chikkaballapur[/TD]
[TD]582[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Chitradurga[/TD]
[TD]566[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Davanagere[/TD]
[TD]567[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Kolar[/TD]
[TD]581[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Ramanagara[/TD]
[TD]584[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Shimoga[/TD]
[TD]568[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Bangalore[/TD]
[TD]084[/TD]
[TD]Tumakuru[/TD]
[TD]571[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Bagalkot[/TD]
[TD]556[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Belgaum[/TD]
[TD]555[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Bijapur[/TD]
[TD]557[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Dharwad[/TD]
[TD]562[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Gadag[/TD]
[TD]561[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Haveri[/TD]
[TD]564[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Belgaum[/TD]
[TD]085[/TD]
[TD]Uttara Kannada[/TD]
[TD]563[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Gulbarga[/TD]
[TD]086[/TD]
[TD]Bellary[/TD]
[TD]565[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Gulbarga[/TD]
[TD]086[/TD]
[TD]Bidar[/TD]
[TD]558[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Gulbarga[/TD]
[TD]086[/TD]
[TD]Gulbarga[/TD]
[TD]579[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Gulbarga[/TD]
[TD]086[/TD]
[TD]Koppal[/TD]
[TD]560[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Gulbarga[/TD]
[TD]086[/TD]
[TD]Raichur[/TD]
[TD]559[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Gulbarga[/TD]
[TD]086[/TD]
[TD]Yadgir[/TD]
[TD]580[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Chamarajanagar[/TD]
[TD]578[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Chikamagalur[/TD]
[TD]570[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Dakshina Kannada[/TD]
[TD]575[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Hassan[/TD]
[TD]574[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Kodagu[/TD]
[TD]576[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Mandya[/TD]
[TD]573[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Mysore[/TD]
[TD]577[/TD]
[/TR]
[TR]
[TD]Bangalore[/TD]
[TD]02[/TD]
[TD]Karnataka[/TD]
[TD]29[/TD]
[TD]Mysore[/TD]
[TD]087[/TD]
[TD]Udupi[/TD]
[TD]569[/TD]
[/TR]
</tbody>[/TABLE]

 

[csanjeev99]

Dear Rick,

I would be grate full if you could please consider my message no# 8.

Also I have serached regarding my problem it could Multiple Validation

 

[pjmorris]

csanjeev, what you wish to achieve, I believe, is possible, but it requires a significant number of steps. Unfortunately I have to work and am away from the office for the next 5 weeks so don't have time to develop a full solution. However the principal would be:

1. Create named ranges for Office, State, Division, District.
2. Create Unique Lists for each something like this for the ShortOffice List: {=IFERROR(INDEX(Office,MATCH(0,COUNTIF($I$1:I1,Office),0)),"")} note that this should be enter in cell I2 with CTRL+SHFT+ENTER as it is an array formula then copied down as far as necessary. For the ShortState list this would be: =IFERROR(INDEX(State,MATCH(0,COUNTIFS($L$1:L1,State),0)),"") entered in cell L2 again with CTRL+SHFT+ENTER and copied down. Short Division is =IFERROR(INDEX(Division,MATCH(0,COUNTIFS($O$1:O1,Division),0)),""). I ran out of time to do ShortDistrict.
3. To the left of each of these short lists lookup the relevant Office, State or Division using something like this in cell K2: =IFERROR(INDEX(Office,MATCH(L2,State,0),0),"") and copied down.
4. Once these are working, you can create dynamic named lists for example for the list of Offices: =OFFSET(Sheet1!$I$2,0,0,COUNTA(Sheet1!$I$2:$I$23)-COUNTBLANK(Sheet1!$I$2:$I$23)), the range I2:I23 is where the shortoffice list resides.

At this point my invention has run out of time, but you should be left with 4 unique lists with to the left of each the name of the superior grouping. It ought then to be reasonably simple to create validation lists based on this information.


Apologies for an incomplete solution, but perhaps others can assist when you can show them where this has got you too.

Best Regards

 

[Andrew Poulsom]

Maybe you can adapt the example here:

Cascading queries

 

[pjmorris]

Thats pretty much the direction I was going in, but felt I needed to start with extracting unique lists. Hopefully the OP will now have enough to solve his problem.

Regards

 

[kvsrinivasamurthy]

Pl see the attached file "Validation ans"
http://www.box.net/files

 

[Rick Rothstein]

Pl see the attached file "Validation ans"
http://www.box.net/files

Are you the Original Poster (OP) for this thread (your name is different, but you are posting like you are the OP)?

Your link requires a login... you need to post the link the website gave you for your individual file, not the link you use to login to your account with.

 

[kvsrinivasamurthy]

I am not OP. I want to post the ans in file, so that it is easy to understand. Sorry for the mistake.

 

[mfexcel]

are you are looking for a dependent dropdown list by data validation.

Debra Dalgleish has many of posts regarding this topic. Pls see if you could find sth that fits your need.
http://blog.contextures.com/archives/2013/09/26/conditional-drop-down-lists-in-excel/