Dependent Drop Down List In Excel

Hello,

I have been trying to create a dependent drag down list however unable to drag it down throughout the whole column.

As shown in the picture, when I select the Column F (Area) it gives me a list of options to choose from in Column G (Sub-Area) however I am not able to drag down past 1 cell.

I used the xlookup function to find the relationship between Area and Sub-Area (=UNIQUE(XLOOKUP($F7,$BQ$1:$CH$1,$BQ$2:$CH$87),,TRUE))

I want to be able to replicate the F7 cell function and drag it till F1000 but simultaneously the Column G should match.

I hope my question makes sense.

ANy help would be much appreciated.

Capture.PNG

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Thanks. Please update your forum profile, which is clearly out-of-date. Thanks.

It is in the validation itself.
It basically does the same of what you were achieving, but the OFFSET function cuts the extra blank spaces.
Notice that I also cleaned the blank cells bellow the itens of the list.

See a video about it:
https://www.youtube.com/watch?app=de...ature=youtu.be

As I told you I needed to clean the cells bellow the itens because they were not really empty.
You need to check if the cells are really empty otherwise it won't work. An easy way to do it is by substituting cells that you think might not be empty for new cells, dragging them.

A test that you can do, select a large range bellow the itens, press F5, select the button special and then select Blank, the range that is still highlighted is the one that does not contain spaces or any other form of invisible character.