In-Cell Drop Down List not properly working

[shimi...@gmail.com]

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

[shimi...@gmail.com]

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

> > > own if the cell is not empty.- Hide quoted text -
>
> - Show quoted text -

[shimi...@gmail.com]

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

[Peter]

In case anyone else has this problem:

I had the same problem with one of my worksheets.

For me the solution was to be found in Microsoft's Knowledge Base article 820741:
http://support.microsoft.com/kb/820741/en-us

Scroll down to 'Use the "Open and Repair" command' and start from there.

1. Start Excel.

2. On the File menu, click Open.

3. In the Open dialog box, select the file that you want to open.

Click the arrow on the Open button, and then click Open and Repair.

[...]