Need Help on Data Validation to return either a value or drop down list and other queries

[ec4excel]

Hi, I have searched the forum but could not find any similar post on this issue and hoped that someone could help.

I need to input or choose an address when a data from a drop down list has been selected.

For Example, I have countries like China, US-A (must be in this way as this is our code), Japan and Singapore. And upon selecting China, I have serveral cities to choose from and for the case if I selected Singapore, instead of a drop down selection showing only Singapore, can the field display only the result as there is only 1 selection and no other options. This is important as most of the account has only 1 address and only less than 10% has multiple addresses.

I had used Dynamic range for the Cities but am stuck with this code "US-A" as we have been using the "-" for the past 10 over years and am unable to change it and the Dynamic range cannot be named with "-".

My next query is, how to make the drop down list on A2 to show only the Countries listed in column F and when I have more countries added, I need not update the Data Validation source on A2 to show more fields, which in my sample file, the drop down list for the countires have many blanks below, which should stopped at Singapore as it was the last country on the list. If Thailand is to be added into column F, A2 last country should show till Thailand without changing the Data Validation source (Ideally).

Thank you.

[benishiryo]

hi ec4excel. for question 1, try naming the Named Range "USA", then in your data validation list for B2:
=INDIRECT(SUBSTITUTE(A2,"-",""))

for Qn2, i don't think you can do that without VBA. and i'm not good in it, so ask this in the Programming area if you don't receive any replies.

for Qn3, use this formula for Named Range Country:
=$F$2:INDEX($F:$F,COUNTA($F:$F))

but to do up an extensive Countries & Cities mapping, it's good to do something like this:
http://www.excelforum.com/excel-tips...t-problem.html

[outofthehat]

To answer your questions as numbered in the workbook:
1. The USA problem. Name the range of cells with the US cities USA. Then, in your data validation, instead of using =INDIRECT(A2), use =INDIRECT(SUBSTITUTE(A2,"-","")). The US-A then has the hyphen replaced with nothing, and the INDIRECT function uses the range name USA.
2. Making Singapore appear immediately. Simple answer is, you can't without macros, and I'm not a fan of macros if I can avoid them. The only way to make something appear automatically in a cell without macros is with a formula, and that would be overwritten with any selection by the user. How about this as an alternative? Use conditional formatting to turn it grey if someone selects Singapore. Then have a separate output cell (e.g. the cell below) which contains =IF(A2="Singapore",A2,B2). Then use this output cell to feed into whatever else you are doing.
3. Enabling ranges to expand automatically. Use either the whole column as your validation list and tick 'ignore blank cells', or the OFFSET function to calculate the range. See here for details:http://www.ooth.co.uk/excel-tips/ran...g-data-ranges/. As a general thing, I put all my reference data on separate sheets so tricks like using the whole column and the OFFSET/COUNTA match don't get snarled up by other data.

[outofthehat]

@benishiryo - I like your solution for Q3!

[ec4excel]

Dear benishiryo , outofthehat , thank you very much for the answers. Actually I had combined few of my usage in this query and post.

At least I had solved the "-" code issue. As for the displaying of a single line when there is no other option for drop down, I will wait if others response in this thread.

I am not really using countries as list as I am only using it as an example. I will try to make the data into a table and see if the drop down list will change accordingly or not as I will be hiding the columns and protecting the worksheet and only unlock cells can be selected and changed.

I had tried to put data into another sheet in the same workbook but when I hide it and locked it, the list and function won't work as if the data has never been there, but when I unhide the worksheet, the list and function is working.

Once again, thank you very much for the reply.