Results 1 to 6 of 6

Thread: Conditional Drop Down

  1. #1
    Neophyte Jamesba1's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Conditional Drop Down



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    Excel Noob here. I'm unsure about making a dependant drop down list. I've got the first drop down, but I can't make the next column dependent on that selection.
    The initial selection is dependent on the left row of a table, (rather than the column selection and I'm prefer not to transpose the table).
    Therefore what is chosen as 'landlord' will dictate the 'survey'.
    Any help would be hugely appreciated and obviously anymore info you need will be supplied asap.
    Thanks
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    See attached.
    Attached Files Attached Files
    Last edited by p45cal; 2019-07-26 at 09:00 PM.

  3. #3
    Neophyte Jamesba1's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Hi,
    Firstly sorry for the late response, but thank you.
    That's exactly what I wanted to achieve.
    Any chance you can give me a little bit of info on how you did this?
    Thanks again.

  4. #4
    Neophyte Jamesba1's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by p45cal View Post
    See attached.
    This is just what I needed but can anyone explain the formula to me....(in simple terms!). Thanks for you patience.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    You already know that you can use a named range in data validation since that's what you've done in cells Sheet2!A2:A12, using the name Landlords. The formula in data validation those cells is =Landlords.
    For Sheet2 column B you can use something very similar. For example, if someone chose Housing One in column A, and if you had a named range called Housing One which referred to the cells Sheet1!B5:H5, you would have the data validation you wanted in column B if you used the data validation formula =Housing One.
    Well, almost, just a small problem with Excel not liking a space in the name of a named range (defined name). If we substitute an underline character for the space, Excel is happy (Housing_One).
    So if we had named ranges for all your landlords we could use the INDIRECT worksheet function to refer to them. Luckily, we don't have to define each name laboriously one by one, Excel has a way of creating its own defined names wholesale using the Create From Selection icon in the ribbon.
    So if we select your table in Sheet1!H2:H8, we can click that icon and create names using the leftmost column (A) as the name,
    Click image for larger version. 

Name:	2019-08-06_223111.jpg 
Views:	8 
Size:	57.8 KB 
ID:	9308
    but guess what?, if a cell in column A contains spaces, Excel defines a name substituting any spaces with an underline character.
    You get this (Landlords is your own named range):
    Click image for larger version. 

Name:	2019-08-06_224010.png 
Views:	3 
Size:	33.3 KB 
ID:	9309
    Convenient. Sort of. Now we can create the data validation formula for all of column B of Sheet2 using the likes of =INDIRECT($A2). This will be fine where there are no spaces in the landlord's name, so to cater for that we'll substitute any/all spaces in that name with an underline character: =INDIRECT(SUBSTITUTE($A2," ","_"))
    That's it.
    Last edited by p45cal; 2019-08-06 at 10:44 PM.

  6. #6
    Neophyte Jamesba1's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Once again, a huge thank you for taking the time to help out.
    Rest assured I will be asking more questions as I get more involved with using Excel!

Posting Permissions

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