Help required for data validation formula

[Bimmy]

Hi,

I came across a workbook which uses below formula in data validation.

=OFFSET(name,MATCH(LEFT(B3,LEN(B3)),LEFT(names,LEN(B3)),0),0,SUMPRODUCT(--(LEFT(names,LEN(B3))=B3)),1)

The data is in sheet2 Column A from Cell 2 to Cell12

Data validation is in Sheet1 Column B.

2 name ranges are being used as below -

Name
Names

Idea is for the user to type the starting character of a word say A and click on the validation arrow which will give them all the possible words starting with A.

Problem -

If I try to add more data in Column A of sheet2, the data validation does not work.

Explanation -

If I add data from Cell 13 onwards in sheet2, data validation does not pick up the data added.

Kindly assist by providing full formula.

Have attached sample sheet for reference.

Note -
I don't have any idea about how the above formula works. I just came across the file while googling.

 

[Chihiro]

Worked fine for me.

One issue though. You have data validation list for Column C set to $C$1:$C$1
that needs to be changed (though it won't impact Column B validation).

But since C is dependent on B, just do lookup instead of Data validation there.

 

[Bimmy]

Hello Chihiro,

Thank you for responding.

I made few changes -

Deleted Column C from Sheet 1 and Column B from Sheet 2
Changed the names in Column B from Sheet2.

Now data validation does not work as stated from my original post.

Have attached sheet.

Kindly advise what changes needs to be done.

 

[Chihiro]

Make sure that the name list in sheet2 is sorted in alphabetical order.

Or the formula/validation won't work.

 

[Bimmy]

Perfect...Thanks Chihiro..