Drop Down List based on last used row

busybee_1_in · Mar 9th 2021

I have an Excel file with data in Column A. My attached file has data from A2:A6. I want to create a drop down list on Cell B2 of Sheet2. I would like to create a list that will be populated with the data in the Used Range in Sheet1's Column A i.e. if this column has additional data in the future it should change dynamically.

I tried to apply a Formula for my Validation Object I found on the following website but it does not work.

Excel Data Validation -- Dependent Lists With INDEX (contextures.com)

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Is it possible to create such a drop down list with a Formula?

**royUK** · Mar 10th 2021

The simplest way to create a dynamic drop down is to format the data as a Table, then create a Named Range based on that Table. I've added it to your example and there's an example workbook of mine demonstrating how to do this.

Overview of Excel tables

busybee_1_in · Mar 10th 2021

Thanks for the help.

The Excel file I posted was only a Testing file. My task involves getting a unique list from a column with merged cells. I utilized the following formula to get a unique list.

=UNIQUE(FILTER(OFFSET(A2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1),OFFSET(C2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1)<>""))

**royUK** · Mar 10th 2021

Well as you use MergedCells you really should have mentioned that. MergedCells should be avoided at all costs

Drop Down List based on last used row

Participate now!

Share

Tags