Conditional Drop Down

Jamesba1

New member
Joined
Jul 24, 2019
Messages
5
Reaction score
0
Points
0
Excel Version(s)
2016
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
 

Attachments

  • EXAMPLE.xlsx
    12.5 KB · Views: 12
See attached.
 

Attachments

  • ExcelGuru10128EXAMPLE.xlsx
    12.9 KB · Views: 15
Last edited:
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.
 
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,
2019-08-06_223111.jpg
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):
2019-08-06_224010.png
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:
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!
 
Back
Top