Sir, I have a issue which is unaddressed, i write to have your valuble suggestions on this. I am working on a data base, for Example data base having country, state, region data validations, i need to extend this validations to two more fields. I think this is possible in Excel., can any senior out there help me in doing this. Let me brief what am i looking at

Cell A1 : Select the country under a list of validation: (For Example INDIA is selected)

Cell B1: The states under the country selected will get displayed (all the states in INDIA will get displayed, For Example Karnataka is selected)

Cell C1: The regions in the state selected will get displayed( for example "Jayanagar", Koramangalam" etc

NOW I want the validation applied to 4th and 5th Cols (D1, and E1) which enables the user to select the street in D1 and Block in E1) which means if Jayanagar is selected in C1, then D1 should display the street names in Jayanagar, and after this Col E1 should display the Blocks in selected Street.

Thanks in advance for the support

Posted 3 years, 3 months ago by Sri Ram

The best thing to do will be give the Tables a name and than while doing the data validation list in cell reference give table reference. But again you have to use macro for that.

If you can provide the excel file, i can surely help you.

But without seing the file i will try to help.

Create Country table.

Create State Table

Create regions rable

Create street table

And in Cell1 when you do the validation list put Country table name instead of cell reference. But again in cellB1, have all states of india.

Without macro its not possible. Please upload the file in thread so can try to help you.

Posted 3 years, 3 months ago by CA Pulkit Sharma

The good idea will be filter option.

First filter the Country, only relevant states will appear, than states and so.

Attaching a file for your reference. 

Posted 3 years, 3 months ago by CA Pulkit Sharma

no need to do this through Macros

you can do this through Data validation with index formula


you can see in above image....if its usefull then please do let me know 

Posted 3 years, 3 months ago by chand khan

Just put this index formula in data validation for cities

Posted 3 years, 3 months ago by chand khan

Your Reply:

You need to be logged in to reply.