Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
Can you post the data validation settings?
Thx, JP On Mar 6, 6:28*pm, wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
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. *** On Mar 6, 4:42 pm, JP wrote: Can you post the data validation settings? Thx, JP On Mar 6, 6:28 pm, wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
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. On Mar 6, 7:31 pm, wrote: 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. *** On Mar 6, 4:42 pm, JP wrote: Can you post the data validation settings? Thx, JP On Mar 6, 6:28 pm, wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
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. On Mar 6, 4:42 pm, JP wrote: Can you post the data validation settings? Thx, JP On Mar 6, 6:28 pm, wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
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 On Mar 6, 10:50*pm, wrote: 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. On Mar 6, 4:42 pm, JP wrote: Can you post the data validation settings? Thx, JP On Mar 6, 6:28 pm, wrote: 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.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
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. On Mar 7, 8:20 am, JP wrote: 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 On Mar 6, 10:50 pm, wrote: 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. On Mar 6, 4:42 pm, JP wrote: Can you post the data validation settings? Thx, JP On Mar 6, 6:28 pm, wrote: 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.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In-Cell Drop Down List not properly working
To quote Dave Peterson, "sometimes the dragon wins."
Good luck, --JP On Mar 7, 12:20*pm, wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use the "Open and Repair" command
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. [...] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with Drop Down List | Excel Worksheet Functions | |||
list validation drop-down not working | Excel Discussion (Misc queries) | |||
But not working properly | Excel Discussion (Misc queries) | |||
MODE isn't working properly. | Excel Worksheet Functions | |||
CNTRL + END is not working properly | Excel Worksheet Functions |