dependent drop down list

[sanlen]

Hi,

I am trying to apply a formula for dependent drop down list which I got from net to my worksheets. Look like it is working fine when I select the region name and then show province name of that region in another drop down list (My columns are in this order: Location, Region, Province)

However, I now would like to do the reverse by select the province then the next drop down list would show value of the locations in that province (My columns are in this order: Location, Region, Province)

I have attached my sample along. Your advice will be very much appreciated.

[teylyn]

Hello,

I fail to see how your attached file has a working dependent data validation. There are many, many duplicates of the same value in each data validation list, which could be avoided by applying the principles outlined in the Contextures tutorial http://www.contextures.com/xlDataVal02.html

Where exactly would you want to see a result for a reverse lookup? Where is the data entry for the parameter that you want to look up?

[sanlen]

Hi,

Thanks!

I would like to have columnA (Location) as the dependence of columnC (Province). i.e: while i select PNH then i would get a list of PNH1, PNH2, PNH3, PNH4. Whenever i select KRT i then would get KRT1, KRT2, KRT3, KRT4, KRT5, KRT6, KRT7, KRT8.

Once again, thanks you very much for any further advice you may give me.

[teylyn]

Take a look at the Contextures tutorial I linked to above. If you get stuck, post what you have come up with.

[sanlen]

Hi,

Look like it is working now (please advice if i am on the right way). My source data is in Sheet1 (Province is in the first column and then location of each province is in second column), and the drop down list is in Sheet2.

However, my actual data which is same formate in Sheet3 which is Location in each province in the first column and then List of province in second column.
I want to use the province to lookup back for location name.

I really cannot figure out how to use the formula that i have and apply it to Sheet3. I have attached the file for your reference.

Could you please kindly advice? Your further comment will be very much appreciated

[sanlen]

Hi! Please kindly help

[teylyn]

Hello,

let ProvinceList point to the range in Sheet3,
=Sheet3!$B$2:$B$30

let ProvinceStart point to the range in Sheet3,
=Sheet3!$B$2

then in the data validation in Sheet2 B2 use

=OFFSET(ProvinceStart,MATCH(A2,ProvinceList,0)-1,-1,COUNTIF(ProvinceList,A2),1)

[sanlen]

Hi,

Thanks you very much for your help.

I am trying with this formula =OFFSET(ProvinceStart,MATCH($C$2,ProvinceList,0)-1,-20,COUNTIF(ProvinceList,$C$2),1) but i always get a wrong list down (i.e: when i select PNH, i then should get PNH1, PNH2,..and whatever value in the same row with PNH, however, i dont get those list as it should. I some time get KDL1,KDL3,...KRT1,..)

I have 24 provinces, and more than 2500 locations in my worksheet.

In addition to the above issue, i am also having problems with the ProvinceList. I am trying to avoid the duplicate province in my drop down list, so i created another sheet which just to keep the 24 provinces. It seems my dependent drop down list disabled when i link the ProvinceList to another sheet.

Could you please advice?

You have been giving big tips of how to settle this, but yet i cannot make it out. I am so shame about this

[teylyn]

Hello,

I think the problem is that you cannot reference cells in another worksheet in a data validation formula in Excel 2007 and earlier.

Instead, you can put the Offset() formula into a range name and use the range name in the data validation.
see attached.

cheers

[sanlen]

Hi,

Thanks you very much for your help.

I am trying to understand your formula and when i come to XFD2 then i get stuck and dont know what it is referring to.

I am trying =XFD2 in Excel 2007 then i got 0, and while with Excel 2003 then i got #NAME?

Could you please kindly advice?

I am only to use Excel 2003 at work.

[teylyn]

Hello,

there was a relative reference in the Offset formula instead of an absolute one. Sorry. Try this attachment. It will work fine in Excel 2003.

[sanlen]

Hi,

I tried to appy them to my actual work, however, i still dont get the correct list. i.e when i select PNH, then i got not only all those location in PNH, but some other location and same problems to other selection.

Would you mind if i send my sheet to your email address? Sorry for this inconvenience.

Please advice!

[teylyn]

You need to make sure that the data in Sheet3 is sorted alphabetically in column B. Then there should not be a problem. If the data is not sorted alphabetically, all kinds of funny results can come up.

[sanlen]

Hi,

It is working now. Thanks you very much for all your help and advice. However, could you please advice what is the different with sorted and without sorted?

[teylyn]

sorted

a a1
a a2
a a3
b b1
b b2
b b3
c c1
c c2
d d1
d d2
d d3
d d4

In this list, the formula will be able to find the first and the last occurrence of a letter and will be able to return the neighboring cells as one contiguous range, which can be used in a data validation list.

In an unsorted table

a a1
b b1
c c1
a a2
b b2
c c2
a a3
b b3
d d1
d d2
d d3

it is not possible to form one contiguous reference for the data validation list, and the list between the first b and the last b will contain many other things, not just b1, b2, b3

Do you see what I mean?

cheers