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

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

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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/