+ Reply to Thread
Results 1 to 4 of 4

Dependent Drop-Down Menu (can't delete spaces)

  1. #1
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Broooklyn, NY
    MS-Off Ver
    Excel 2016
    Posts
    207

    Dependent Drop-Down Menu (can't delete spaces)

    I am trying to create dependent drop-down menu. When I go to formulas and select Create from Selection, I have to select I2-J5, otherwise it will give me an error message. I don't want blank cells when I select No. Can you help? I highlighted cells in yellow.
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Dependent Drop-Down Menu (can't delete spaces)

    One way would be to use tables to create dynamic named ranges that will resize as you add/delete values from the drop down lists.

    See attached.

    BSB
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Broooklyn, NY
    MS-Off Ver
    Excel 2016
    Posts
    207

    Re: Dependent Drop-Down Menu (can't delete spaces)

    Did you use pivot tables? Can you walk me through how you created the table?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Dependent Drop-Down Menu (can't delete spaces)

    Those are not pivot tables, they are Excel tables.
    To make the 'yes' table (table1) select I2:I6 and press the Ctrl and t keys. Be sure that "My table has headers" is checked. Repeat the process with the 'no' range J2:J3 (table2).
    BSB then produced named ranges for 'yes' and 'no'. On the formula tab select 'Define name' > in the Name window of the New Name dialog box type Yes > in the Refer to window type =table1
    The same process should be repeated for 'No' (=table2)
    The data validation for cell B2 is: =$I$2:$J$2
    The data validation for cell C2 is: =INDIRECT(B2)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Cell value dependent on a drop down menu
    By Ray522 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2019, 12:11 PM
  2. [SOLVED] How do you change which drop down menu appears dependent on entry in another cell?
    By zhunter71 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2018, 02:53 AM
  3. Drop Down Menu Dependent On Another Cell
    By bwils39 in forum Excel General
    Replies: 2
    Last Post: 03-30-2017, 12:15 AM
  4. Help requested on dependent drop down menu
    By jun-xin.chen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-30-2015, 06:03 PM
  5. Replies: 4
    Last Post: 09-11-2014, 10:38 PM
  6. Replies: 9
    Last Post: 04-29-2013, 11:58 AM
  7. Replies: 2
    Last Post: 02-13-2006, 12:00 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