+ Reply to Thread
Results 1 to 9 of 9

Drop Down List that an Item can only be chosen once.

  1. #1
    Registered User
    Join Date
    03-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Drop Down List that an Item can only be chosen once.

    I have looked and looked and really cannot find what I am looking for. Here is my dilemma... I have two worksheets on Excel 2010. On worksheet A, I have a list ** books Book A, Book B, Book C and so on. On each ** these books I have them numbered by how many copies I have. Book A has numbers 1, 2, and 3. Book B has numbers 1, 2 ans so on.

    On worksheet B, I have where a person is listed that has "checked out" this book (like a library). Person #1 has "checked out" book A #1 and book B #1 . Person #2 has "checked out" book A #2 and book C #4 ...

    I have used data validation drop down list on the book titles on one cell and then did a indirect function drop down list for their numbers on another cell. What I am needing is a way to make the numbers on the 2nd drop down list either flag that the number ** the book was already chosen by a person or disappear from the list when selected.

    I am not very good at excel and only know a few basics. Could someone please help.

    Thanks!!

  2. #2
    Registered User
    Join Date
    03-07-2014
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    21

    Re: Drop Down List that an Item can only be chosen once.

    Hi - Can you attach the workbook you're referring to? There is a way to build conditional validations and it seems like you're headed there.
    Regards,
    Hansens 0ffice dot com

  3. #3
    Registered User
    Join Date
    03-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop Down List that an Item can only be chosen once.

    I don't have the exact one I am working on.. different computer, but I have made a rough draft ** what I am doing. I am hoping that in the "Book Number" the numbers to the book chosen can disappear or be flagged when that book is chosen for another person.

    Thanks for looking at this for me!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop Down List that an Item can only be chosen once.

    I have seen this website http://www.contextures.com/xlDataVal03.html
    But I do not know if this is what I am needing or how to apply this to my worksheets.... I am not good at formulas.

  5. #5
    Registered User
    Join Date
    03-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop Down List that an Item can only be chosen once.

    Still looking for help please.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Drop Down List that an Item can only be chosen once.

    If you scroll down on Debra's website, she shows you how to only use the name once. Follow her instructions.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Registered User
    Join Date
    03-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop Down List that an Item can only be chosen once.

    Quote Originally Posted by alansidman View Post
    If you scroll down on Debra's website, she shows you how to only use the name once. Follow her instructions.
    This really does not help at all.... I have multiple numbers on hundreds of books. She uses one list of names. I do not know how to add it to my 2nd drop down list. Plus I do not really want the extra worksheets that shows the numbers I did use and the numbers that I did not use.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Drop Down List that an Item can only be chosen once.

    Perhaps if you listed your books as:

    Book A cpy 1
    Book A cpy 2
    Book A cpy 3
    Book B cpy 1
    Book C cpy 1
    Book C cpy 2
    etc.

    Set up a total list and track that way with just the one list. This would be similar to setting up a normalized table in a database.

    You could then use her concept or use this concept that she demonstrates for party table seating.

    http://blog.contextures.com/archives...ng-with-excel/

    or you could use one of the MS templates for a lending library

    http://office.microsoft.com/en-us/te...104022389.aspx

    Just some alternative thoughts for you. Good luck with your project.

  9. #9
    Registered User
    Join Date
    03-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop Down List that an Item can only be chosen once.

    Thanks, I will check out different ways to list the books, and the lending library.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hide/Unhide columns in multiple worksheets based on item chosen in a drop down list.
    By Gattaca2014 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-27-2014, 01:31 PM
  2. [SOLVED] Auto fill line when item is chosen from drop down list.
    By ericabutkovich in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-09-2012, 05:47 PM
  3. Replies: 3
    Last Post: 04-24-2012, 05:18 PM
  4. auto populate cell when selected drop down item chosen
    By palmer19 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-13-2010, 07:16 AM
  5. Determine which item in a list box was chosen?
    By max3732 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2010, 01:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1