Dropdown List Based on Criteria

[cychua]

Hi All
Cracking my head for almost 6 hours in the office on this dropdown listing.

For hours of google-ing. Found this helpful links, http://contextures.com/xlDataVal15.html

But i able to make the dropdown as,
Region > Country > Country

Instead of,
Region > Country > Port

Together i had attached with my files. Hope you guys able to help a noob out.
Thanks in advance.

[Jacc]

Wow! That's the most advanced dynamic named ranges I have ever seen!
I figured out how it works for countries and then I just copied that concept to ports.


Edit:
I have now read the article in the link you posted. That sample only works if the data validation cells are next to each other. Since you are using merged cells it will not work as described in the article. My solution avoids that problem but you have to use more variables.

[cychua]

Dear Jacc
Thanks for your reply and guidance.

What if i would like to add 1 more row with the same function.

1. Current listing which is fixed by adding more variable :-
D4>F4>I4 = Region > Country > Port


2. Add on 1 new row with same listing functions
D5>F5>I4

What should i do?

[Jacc]

You are welcome! Sorry I missed your post.

I added variables and datavalidation for POD as well.

Your country and port data had some errors (for example it said America one place and Americas another) that would cause datavalidation to malfunction.
I corrected that and I added all the countries, even the ones with no ports.

I also threw in a little macro that sets the countries and ports when region is changed, or just port when country is changed. This is to avoid to get things like America -> Egypt -> Bangkok.

[cychua]

Hi Jacc
No worry. Good to see you again.
All those error will occur when i did not place good on the database only right? (Sorry i am totally noob in VB)

The macro you add in, is to point out the valdata which i had not key in probably/accurately only right?

I had furnished my original workbook as attached, before your post. It seems like sheet on "Error Checking" is good and brilliant work from ya. anything goes wrong may check through the table with "0".

[Jacc]

When you change the region, the macro automatically selects the first country in that region.
When you change the country the macro automatically selects the first port in that country.
It prevents it from showing Egypt when America is selected. It is not really necessary but it will actually save you one click for the countries that only have one port.

Yes the errorchecking just makes sure that you all the countries between the two lists match.

[cychua]

Greatly appreciated Jacc.

By the way, on the sheet"Error Checking" The most left part with the formula as

Please Login or Register  to view this content.

What is this for? i saw some of my the return to me as 0, 2, and 1.

[cychua]

On the macro, 1st row not working, but 2nd row, in order. This is the weirdest thing ever...

Please Login or Register  to view this content.

[Jacc]

Please Login or Register  to view this content.

You mean IF(P2="-","-", ? The formula calculates the whole area with lots of empty cells (or rather cells with "-"). If I didn't have the first part it would return zero on those cells too. With the IF(P2="-","-", part it simply excludes those cells that contain "-".
By returning a "-" I want to mark that there is a formula in the cell but the result is not relevant.

If it returns "-" that means the result is not relevant. I could have make it return just an empty cell but the problem then is that you don't see that there is a formula in the cell.
If it returns 0 it means that there is no match and something is wrong. You either forgot to fill in a country or you spelled it different.
If it returns 1 or 2 it is ok. The 2 is because the port of Singapore is also called Singapore or something like that.

If the macro is not working it is probably because one of your named ranges is not defined. This is probably because there is a country missing or miss spelled somewhere. Check if the error check sheet returns any 0.

[cychua]

Solved. After re-validate it. ^^