trouble in the library

I have a strange situation I can't figure out. I created a spreadsheet to track pamphlets given out to patients in our library. As the list of pamphlets grew it became more cumbersome. At some point I created a reference table which a listed most of the more popular titles distributed. The table was names “listnames”.

I don’t remember how I set it up but last year it worked perfectly. I was able to left click on column L:, a drop down menu would appear including the option to select “Pick from Drop-Down list…”. When I clicked on this option a drop down menu would appear with a list of all the titles listed on the reference table “listnames”. I could also type a different title into the cell if the pamphlet given out wasn’t available in the list.

At the end of 2016 I erased all data from the spreadsheet so I could start fresh for 2017. Now when I right click on a cell in column Land click “Pick from Drop-Down list…” I sometimes get the list of titles I’m looking for and sometimes I get a blank box. And every cell in column L is different. In one cell I may get the list and in another I get that empty box.

Does anyone know what the issue is or what direction I need to focus on to troubleshoot thisproblem. Thank you very much for any help you could provide.
Sincerely,
Karl E

Usually a dropdown list derives its "data" from a table located either on the same sheet or another sheet.

Was that info deleted as well ? Or was it altered ? Or perhaps some of it was moved a cell or two ?

Thank you for your help!!!!

Unfortunately I didnt erase or change any of the data from the table (it is located at the very top of the sheet). And that's part of what perplexes me. The entire list of titles will appear as expected when I click on "Pick from Drop-Down list..." in some cells in the column and not at all in other cells. For a while I was attempting to troubleshoot it as a formatting or properties issues related to the individual cells. Im at a loss. Again, I appreciate even your consideration of my problem. Thank you so much.

Karl

I will do that as soon I can. We go to bed fairly early and have a full day planned at work tomorrow. Hopefully will have that upload ready tomorrow by 7 or so. Thank you for being willing to take a look.
Sincerely,

Karl

You should be able to attach a file to one of your posts, but you should note that the Paperclip icon does not work (there are a few other software problems on this site).

To attach a sample Excel workbook, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

Sadly I'm still having difficulty. I've gotten through most of the process until I attempt to upload the document. Because its an excel file its limited to 1000KB. I havent been able to get the spreadsheet down to less than 2.2MB without loosing functionality. I'm sure you could help but I may not be able to get the opportunity for you to take a look at it. I will keep working on it. Thank you so much for your help.

Karl

Ok, I did find a way to post the spreadsheet to the forum (in a zip file). If you go to the usage tab and then down to Column L Row 1011. Right click in this cell and you will see a dropdown menu appear with the option "Pick from Drop-down List...". When you click on that it gives you a list of titles referenced from the table on the "Names" tab.

Next if you go down one row to Column L Row 1012 go to the "Pick from Drop-Down List..." you will see that no list appears (just and empty box).

I could use data validation to create a dropdown box but i would then be limited to entering only information contained within the referenced list into the cell. Right now i can type an entry (like notes) into the cell instead of using information contained in the list if necessary.

If you move up and down the column L and choose "Pick from Drop-down list..." option you will see some cells result in an empty box and some cells reference the list of titles.

This has been very frustrating. Does anyone have any ideas on how to fix this or what might be going on. Thank you for any help or suggestions.

Karl

I cant say I completely understand the way this feature works but based on some of the replies and infomative help yall offered it did help me stumble upon a solution. I learned that if I enter a simple "-" into the previous cell the list I'm looking for appears in the following cell. And this works consistently. Once I added a "-" to each cell in the K column, when I right click on the L column and select "pick from drop-down list..." the complete list appears. No longer do I get just an empty box with no options. Thank you all for your help.

Karl E