Advanced Validation Required

csanjeev99

New Member
Joined
Aug 17, 2014
Messages
9
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]
 
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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/
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top