+ Reply to Thread
Results 1 to 16 of 16

Remove selected options from dropdown list

  1. #1
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Remove selected options from dropdown list

    I used contextures tutorial but as much as I try cannot expand it from 1-6 cells to 1-32 whenever I do the the unused names stops working anyone help me with the formulas I used all of the ones in there tutorial
    http://www.contextures.com/xlDataVal03.html
    Thank you so much for your time and I apologize if I posted incorrectly I'm just starting
    Will upload my excel sheet when I get to a computer
    Last edited by Badking333; 11-22-2013 at 04:37 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,291

    Re: Remove selected options from dropdown list

    Welcome to the Forum!

    We've got to have your file to diagnose this. But I read the description at the link and here is the most likely error, if you are using Option 1:
    Option 1: Multi-Cell Formula

    Select cells C1:C6
    Enter the following array formula (the formula is long, and should be all on one line)
    =IF(ROW(A1:A6)-ROW(A1)+1>COUNT(B1:B6),"",
    INDEX(A:A,SMALL(B1:B6,ROW(INDIRECT("1:"&ROWS(A1:A6))))))
    3. Press Ctrl+Shift+Enter to enter the array formula in cells C1:C6
    This is a special type of formula called an array formula. Did you use CTRL-SHIFT-ENTER to enter this formula? If so it will have {brackets like this} around it when you see it in the formula box (not the cell--the formula box below the ribbon). If not, then it won't work.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Remove selected options from dropdown list

    Here is a file that I saved (possibly from this forum) that drop items from the drop down list as they are used.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    Quote Originally Posted by 6StringJazzer View Post
    Welcome to the Forum!

    We've got to have your file to diagnose this. But I read the description at the link and here is the most likely error, if you are using Option 1:

    This is a special type of formula called an array formula. Did you use CTRL-SHIFT-ENTER to enter this formula? If so it will have {brackets like this} around it when you see it in the formula box (not the cell--the formula box below the ribbon). If not, then it won't work.
    Badking
    I will as soon as I get home thank you for your time
    i did that but when i used the dropdown list the used names would not remove but i could have messed up the code when making it 1-31 cells from the 1-6
    Last edited by Badking333; 11-23-2013 at 01:55 AM.

  5. #5
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    Quote Originally Posted by newdoverman View Post
    Here is a file that I saved (possibly from this forum) that drop items from the drop down list as they are used.
    thats perfect how do i make it through 32 not 8 people though

  6. #6
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    DataValHidden tester.xls
    this is my setup
    i had it working 1-7 but then when i tried to make 1-32 i messed it up

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,291

    Re: Remove selected options from dropdown list

    See rewrite below.
    Last edited by 6StringJazzer; 11-23-2013 at 09:19 AM.

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,291

    Re: Remove selected options from dropdown list

    Rewrite of my explanation, file attachment is still good.

    There are two problems. The dynamic named range NameCheck has two versions, one with scope restricted to sheet "Employees - Revised Formula". This will not be visible in "Schedule" unless explicitly named. I removed it. The other one has scope Workbook which is what you want. I also modified that formula a bit.

    I ignored "Employees - Revised Formula" and updated "Employees - Original Formula". The formula in column C of "Employees - Original Formula" needed to be updated--it referred to $B$1:$B$6 but when you go to 32 names it needs to be $B$1:$B$32.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Remove selected options from dropdown list

    Here is the file modified to handle 32 names.

  10. #10
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    Quote Originally Posted by newdoverman View Post
    Here is the file modified to handle 32 names.
    thats perfect thank you where is the star

  11. #11
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    6 string jazzer
    when i use the names after the 6th cell it doesnt remove from the list thanks anyways newdovermans works fine
    that means i would have to edit the namecheck correct??? so it doesnt only work on 1-6 on the schedule file when i do data valadation on 1-32

  12. #12
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    I have a question I noticed it stops working after the ninth person where is the code to edit this

  13. #13
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    Re: Remove selected options from dropdown list
    I have a question I noticed it stops working after the ninth person where is the code to edit this

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Remove selected options from dropdown list

    This will fill in all 32 names and the drop down list will shorten as the numbers are used up. I have also added notes in text boxes showing the formulae used for each area of the calculations.

    For text values use the file for text values.
    Last edited by newdoverman; 11-26-2013 at 05:34 PM.

  15. #15
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,983

    Re: Remove selected options from dropdown list

    Change range in column G and I

    Change also in Name manager the formula 'list' a little bit:
    Change 'COUNTA' in 'COUNT' and there are no empty spaces in dropdown box.

    Same solution a little to late
    Last edited by popipipo; 11-26-2013 at 05:22 PM.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  16. #16
    Registered User
    Join Date
    11-22-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove selected options from dropdown list

    Thanks you guys are the best will try when I get home

+ 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. Replies: 2
    Last Post: 08-05-2013, 10:08 PM
  2. Cull dropdown list options based on selection in another dropdown
    By Kiffar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2012, 01:53 AM
  3. Dropdown list options/Auto-filtering?
    By akdr in forum Excel General
    Replies: 2
    Last Post: 08-29-2012, 10:53 AM
  4. Filter data using dropdown list options
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-02-2010, 05:06 AM
  5. Dropdown list options
    By Back2Basics in forum Excel General
    Replies: 4
    Last Post: 02-10-2009, 10:04 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