I made an Excel worksheet that contain multiple worksheets. One of
them is a list of products containing multiple columns for different
parts. One of such parts is "LENS" column in which I use data
validation to have users select lenses from a pre-set Lens_List which
resides within the same worksheet.

There are a few problems I'm experiencing.
1) Though the set up, the arrow button does not appear when a cell
with a data validation setup is selected. I bring up the list by
pressing "ALT"+Down Arrow to go around it.

2) The more serious problem. The drop-down list (by pressing "ALT+DOWN
ARROW") shows entries in the same column, not in the specified
"LENS_LIST". As a result, the drop-down list even shows the "title" of
column, but still missing items that have not been entered in the
column. Also I fount the drop-down list does not show the entry of its
own if the cell is not empty.

Other Observations;
*In the same worksheet the same problem persists if I test-create
other data validations.
*In other worksheets in the same Excel file, data validation works
just fine.
*The worksheet with this problem does not use "Freeze Pane".

Can anyone tell me how to fix it so that I can use data validation in
the worksheet?
Thanks.