In-Cell Drop Down List not properly working

[shimishake]

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.

 

[JP]

Can you post the data validation settings?

Thx,
JP

 

[shimishake]

Thanks, JP, for your response.

It is set up like this;

SETTINGS TAB
Allow: List
Source:=$X$5:$X$64

Checked: Ignore Blank
Checked: Incell dropdown

I have created numerous files under different names in the same folder
which sprung off from this original file that has this problem.
Those created before Jan 28th 2008 seem to have the same problem.
Anything after (and they were all created earlier today) all DO
HAVE the problem. Regardless, all of these sheets have the same Data
Validation setups, and of course, the reference list at the same
location.

I tried to copy the same column from the working worksheets to the
troubled, using Paste Special transplanting the "Data Validation"
properties only.
Didn't work. It seems as if I turned off the master switch for data-
validation somewhere by accident...

Have any ideas?

Thanks.

***

 

[shimishake]

Correction

I have created numerous files under different names in the same folder
which sprung off from this original file that has this problem.
Those created before Jan 28th 2008 seem to have "NO PROBLEM".

Anything after (and they were all created earlier today) all DO
HAVE the problem. Regardless, all of these sheets have the same Data
Validation setups, and of course, the reference list at the same
location.

 

[shimishake]

Thanks, JP, for your response.

It is set up like this;

SETTINGS TAB
Allow: List
Source:=$X$5:$X$64

Checked: Ignore Blank
Checked: Incell dropdown

I have created numerous files springing off this troublesome original
file
(under new names) in the same folder.

I just found out those created before Jan 28th 2008 have NO problem.
Anything after (and they were all created just today) DO HAVE the
problem.

The originals file is constantly re-saved with minor changes, mostly
improvements in appearance and other functions.
The only changes made in the worksheet though, is to add new item
entry at the bottom of the item list.
It seems as if I turned off the master switch for data validation by
accident in doing so.

Regardless, all these files have the same Data Validation setups, and
of course, the reference list at the same location.
The only difference between the originals and off-springs is that they
have LESS worksheets than the original which has
total of 24 worksheets to accommodate different potential situations
of usage. The off-sprung files have far less worksheets
as I deleted all unnecessary vendor sheets before saving under new
names.

Finally, I tried to copy the same column from the working worksheets
to the original, using Paste Special "Data Validation"
but it didn't work.

Have any ideas?

Thanks.

 

[JP]

I created a list of random numbers in X5:X64 and set up cell E4 with
the data validation settings you supplied, and was not able to
duplicate your problem.

I presume X5:X64 is the "Lens_List". What sort of information is in
those cells?

--JP

 

[shimishake]

JP,

I have transplanted the working worksheet from the past off-spring
file to the original and fixed the problem
sort of a manual way. It seems that I have a working data validation
now. It seems the particular worksheet
was corrupted and lost data validation function. If such is possible.

Thanks, JP.

 

[JP]

To quote Dave Peterson, "sometimes the dragon wins."

Good luck,
--JP