3

I have a 3 column table of data and I need to create drop down list based on these 3 columns.

  • Column 1 has duplicate values.
  • Column 2 also has duplicate values. The options are dependent on the value chosen from drop down 1.
  • Column 3 is dependent on the options chosen in both columns 1 and 2, while only displaying the unique values from each column.

I have found ways to limit the options in the drop downs based on the previous selection, but can't find anything to limit the options in column 3 based on the selections from both 1 and 2.

Is this something that can be accomplished without the use of VBA? The workbook must be in an XLSX format, so it cannot contain any macros.

Example:

enter image description here

If a user selects John then only North and West should appear in drop down list 2.

If user selects John and West, then only South should appear in drop down list 3.

  • You marked this with excell 2007. I guess that this means that functions from newer excel versions are not allowed? Also, what did you already try? What is the precise part where you get stuck? – Hennes Mar 24 '16 at 18:54
  • Just guessing here, but: 1a) You know the selection from column1, so you check search if which rows match that selection. And based on that you select from a copy in B. 1b) You can remove duplicates. So you selection from B should be possible without VBA. 2a) You can repeat the same selection as 1a on row 3. 2b) Rather than removing duplicates you can remove what was selected in row B. 2c) you already can remove duplicates. Based on that: Yes, I think that you can do it without VBA. – Hennes Mar 24 '16 at 19:00
  • Try using Data Validation with Define Name and INDIRECT – user555689 Mar 24 '16 at 19:48
1

You can do it using Data Validation Create First Column A without Duplicate, Then the second for example John is the heading and down it North, West select North and West and Define Name as John, do the same for North and West (write North as header, under it the values you want, select all values and Define Name as North) after in the Cells where you want the Drop Down List L1 start in the first column with normal Data Validation List and using your first column, in the second column M1 use also Data Validation List but in the Source write INDIRECT(L1) and repeat the same for the third Data Validation in N1 Source should be INDIRECT(M1)

| improve this answer | |
  • This appears to be on the right track, but there is one condition that I can't seem to get right. In the example, you'll see that selecting John and North, you get options South and West. However, if you select Mark and North, Upper now becomes an option and West does not. How would I go about limiting the "North" results based on the Sender selected in L1? – Rellek Mar 24 '16 at 21:52
  • Would a "helper" column be appropriate, like concatenating columns 1 and 2 and creating a list of options under the concatenated header? – Rellek Mar 24 '16 at 21:59
  • It is better to write the options corresponding to each value in column 1 alone for Mark what are the options in column 2 – user555689 Mar 24 '16 at 22:02
  • The problem is the third column has crossing values South, Upper, West for Mark, but for John the values are South and west you can write error message and stop it – user555689 Mar 24 '16 at 22:07
  • Ok, that's exactly where I'm stumped. How do I go about adding that error message? – Rellek Mar 24 '16 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.