00:00here's a really cool question check this out in my previous excel tips and tricks video i
00:04demonstrated how you can set up dependent drop down list from a vertical data set in excel but
00:10someone had inquired how you can set up the same dependent drop down list from a horizontal data
00:14set well here's how you do it i'm first going to flip my data set table the vertical data set
00:20table
00:20into a horizontal table that means set a few rows here and essentially what i'm going to do is going
00:26to copy my existing vertical data set like this and then place the cursor down here right click
00:33and basically do a copy transpose like this and after that i can actually get rid of my old vertical
00:40table by simply highlighting and delete now going to the country itself you have a previous formula of
00:48this uh what you have to do is to insert a second argument do true well what true does is
00:55that it
00:56returns a unique column hit enter now you only see us and india that's all i have in terms of
01:02country
01:03but for state you're going to have to do a bit of modification here on the filter here this condition
01:08is incorrect right now so you're going to have to basically point to country here on cell b2 and then
01:14on the indexing here we are basically going to return a row row number two and then get rid of
01:23the
01:23column numbering because we want the very first column and for the unique we're going to basically
01:29say true like before uh basically returning the unique column and you hit enter here and for the
01:36series we're going to do the same set of things on the filter condition we're going to point to state
01:41here and then the second argument for the filter we want row number which is going to be two and
01:49then
01:49for the column number we're going to leave it as blank and for the unique the second argument would
01:54be true so that you will return a unique column hit enter now a couple of things to do here
02:00uh for
02:01the drop down list for the country now we have a new list here so basically going to go to
02:06data data
02:07validation and in here you're going to change the source to be this one here so basically i'm going to
02:14make it a bit bigger so that in future i can expand it all the way to maybe z like
02:19this and hit enter
02:20twice and then for the state drop down data validation again one more time the source is going to be
02:29same
02:30thing i'm going to go all the way to z like this and hit enter twice and then as for
02:38the final one the city
02:40same thing data validation source and basically go to z like this or maybe more doesn't matter so z
02:51like this and hit enter twice and then that's pretty much it for setting it up now to do a
02:57bit of testing
02:58we're going to select india you can see that the drop down list changes according to what you have
03:03selected in a country now if you go to states you can see you can only see states that's in
03:08tamil naidu
03:09let's go to uh us here and then you can see the states are all us states maybe go say
03:16michigan
03:16and then for city sure flint like that
Comments