+ Reply to Thread
Results 1 to 10 of 10

Drop down cells and Data Validation

  1. #1
    Registered User
    Join Date
    08-10-2019
    Location
    Cagayan de Oro, Philippines
    MS-Off Ver
    2016
    Posts
    5

    Drop down cells and Data Validation

    Hello! Relatively new here and still learning how to use Excel efficiently. Forgive my English also.

    Say I want my co-worker to select data from a drop down list I've created. He can select as many as data he can in the preceding cells but should not duplicate on the whole column. Is there any way I can achieve that?

    Otherwise, I was wondering if Excel can remove the data that has been already selected from the next drop down list. I basically want my co-worker to choose data from my table in a drop down list type but is not allowed to have duplicates on the next row. Thank you!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Drop down cells and Data Validation

    I am not sure what you want - but take a look her. happy to explain. If its not what you want, then try to explain again. Consider attaching a sample Excel file, showing what you expect to see.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    08-10-2019
    Location
    Cagayan de Oro, Philippines
    MS-Off Ver
    2016
    Posts
    5

    Re: Drop down cells and Data Validation

    Quote Originally Posted by Glenn Kennedy View Post
    I am not sure what you want - but take a look her. happy to explain. If its not what you want, then try to explain again. Consider attaching a sample Excel file, showing what you expect to see.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    THIS IS AMAZING! THIS IS EXACTLY WHAT I WANTED!!!! THANK YOU!

    I am still studying it and I'm still kind of confused especially on your formula on the IFERROR. I was wondering if you have a detailed explanation on how to execute this. I am really thankful! THANK YOU!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Drop down cells and Data Validation

    Choices, B2 =COUNTIF(Entry!$1:$200,A2)
    simply counts whther or not you have used any given choice on your DD list.

    Choices, C2 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$10)/($B$2:$B$10=0),ROWS(C$2:C2))),"")

    Red: Where the count is zero
    Orange: return the row number
    Green: in ascending row number order
    Cyan: starting at the lowest (this is just a counter and increments 1,2,3 as you drg it down)
    Blue: and return the corresponding value in column A
    Once it runs out of answers it returns an error
    Black. When it returns an error, it gives a blank.

    Then there is a named range (CTRL-F3 to view/edit):

    =INDEX(Choices!$C$2:INDEX(Choices!$C$2:$C$10,SUMPRODUCT(--(LEN(Choices!E$2:E$10)>0))),)

    This is used to list the choices in the cells with data validation in the other sheet. It will return everything from (red) C2 all the way down the column (orange), providing that the length (no. of characters) in each cell is >0 (cyan).


    End result, you only choose from those values that have not been used before and, as you choose more and more values, you do not have a dropdown box that consists mostly of blank cells.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  5. #5
    Registered User
    Join Date
    08-10-2019
    Location
    Cagayan de Oro, Philippines
    MS-Off Ver
    2016
    Posts
    5

    Re: Drop down cells and Data Validation

    Quote Originally Posted by Glenn Kennedy View Post
    Choices, B2 =COUNTIF(Entry!$1:$200,A2)
    simply counts whther or not you have used any given choice on your DD list.

    Choices, C2 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$10)/($B$2:$B$10=0),ROWS(C$2:C2))),"")
    One last question if I may say. What does the 15 and 6 signify?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Drop down cells and Data Validation

    15= SMALL (array form)
    6= Ignore errors.

    read up about them by Googling Excel AGGREGATE function.

  7. #7
    Registered User
    Join Date
    08-10-2019
    Location
    Cagayan de Oro, Philippines
    MS-Off Ver
    2016
    Posts
    5

    Re: Drop down cells and Data Validation

    Quote Originally Posted by Glenn Kennedy View Post
    15= SMALL (array form)
    6= Ignore errors.

    read up about them by Googling Excel AGGREGATE function.
    I just read them and I am still experimenting with them! Thank you!

    My current problem now is that I have done all of the formulas and when my co-worker selected a choice in the sheet and chooses another one in the next, sometimes, in the drop down menu, the one that is selected is still present. In the choices tab, visually, they are working quite well, the Count column is working, the Remaining column is also working. How can I fix this?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Drop down cells and Data Validation

    The only way to fix that is using VBA Is that OK?

  9. #9
    Registered User
    Join Date
    08-10-2019
    Location
    Cagayan de Oro, Philippines
    MS-Off Ver
    2016
    Posts
    5
    Quote Originally Posted by Glenn Kennedy View Post
    The only way to fix that is using VBA Is that OK?
    VBA eh. That's alright

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Drop down cells and Data Validation

    Sorry, I've been away for a bit. Having jus re-read your request, I think that you MUST have made a mistake. Double check that the named range is pointing to the correct sheet:

    =INDEX(Choices!$C$2:INDEX(Choices!$C$2:$C$10,SUMPRODUCT(--(LEN(Choices!E$2:E$10)>0))),)

    Sometimes named ranges mess up by themselves!! If it's OK, then repost your sheet showing the problem, here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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