3 dependent drop down problem. Region-Country-City. Help !!

**specialfx** · 04-02-2013, 02:18 PM

I am having a problem making the 3rd cell dependent on the first 2 cells. see attached, hopefully self explanatory.

dynamic_validation.xlsx

**micope21** · 04-02-2013, 04:03 PM

Hi

Welcome to the forum

Please see the file.

More information from this website
http://www.contextures.com/xlDataVal02.html

**newdoverman** · 04-02-2013, 07:53 PM

This might help:

**specialfx** · 04-04-2013, 12:44 PM

you nailed it. well done

have some questions though.

1. do you create table 1 from insert-table-then highlight all the cells ?
2. how can i make the continent, region and city table dynamic. ie right now i cant add a region or city after row 6 as thats the last row in the table (table1). i tried to delete the table (table1) from design convert to range. then insert table - so as make the range larger but for some reason it still wont allow me to add after row 6 !!. seems like table1 cant be deleted ? whats going on ?
3. i guess this =INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0)), refers to =INDEX(Table1,0,MATCH('Input Data'!C5,Table1[#Headers],0)) ?
4. i guess this =OFFSET(myItemList,0,0,COUNTA(myItemList),1) refers to this =OFFSET(allCountries,0,0,COUNTA(allCountries),1). if so clear

thanku V MUCH !

**newdoverman** · 04-04-2013, 03:44 PM

Questions #1 and 2 Create your table just like in your example. Click in the table then click on the Insert tab, click Table select that your table has headers and click OK. The table will be generated for you.

When you have the table created, you can add a row by clicking in the last column of the last row of the table and then press TAB. That will insert a new row into the table. If you want to add a new column just type the column Title in the next available column next to the table and it should be incorporated into the table. If it doesn't happen automatically, there is a little "nub" on the lower right of the table, left click and hold and drag to include the column you just added.

To answer #3, you should click in the first data cell of your table on the Input Data sheet (I don't have your sheet in front of me so I am going by memory)

Question #4, You are correct. Use the OFFSET(allCountries.....this is what makes your drop down lists dynamic. The other formula using myItemlist was taken out of the help files as I was getting a bit mixed up myself. If I remember correctly, there were directions for making the name in the Name Manager called Countries. You use Countries in your Data Validation. Use List and in the formula field enter =Countries

I should have deleted those formulae from the first page. I created those formulae without being in the formula field so that I could take my time and get the reasoning worked out. Just delete them as they don't contribute to the solution after I worked out the logic...sorry for the confusion.

When you add new continents, countries and cities, start off with the Continent and add the continent name, then add a column for that continent so that you can add the countries for that continent. Once the countries are added, add a column for each country so that you can add the cities for each country. You don't need to insert the columns in the middle of the table, you can just add them on the right hand side if you like as it is easier.

One note about the order of the columns. Don't try to sort the columns using the sort facility because if you do sort on one column, all the other columns go out of order. It is best to think ahead and enter the items to the columns in the order that you want them to be in. You can manually sort each column without disturbing the other columns by moving cells down, thereby giving room for new cells or the "shuffling" of the cells to get the order that you want.

Good luck.