Excel-Drop down list that will change dynamically based on a criteria

Solution 1:

I tried my best to solve it. I have the file ready.

Actually, you need some helper columns to help the dropdown on cell E1. I've put the .xlsx file on Google Drive. Here's the file I've created with all the required helper columns. Just don't try to edit the view the file with Google Sheets, since there might be some functions in use which Google Sheets doesn't recognise. Download the file and open it with MS Excel.

The file looks like this:

There is no VBA involved in this file. So you can just copy the formulas from here and create your own file too.

Here's how to use it: You enter any value which is in Column A, into D2. If you enter a, the dropdown automatically changes to showing all the corresponding "Options" which is in Column B, of all instances of a. And the dropdown updates dynamically if you enter b or c in D2.

But, There are some drawbacks to this. There will some be extra blank options in the dropdown in E2. It's because I've created the function in J2 and all its cells such that, it automatically adds any new entries in A and B columns. One more drawback is when you have a new name, like, d, you will have to enter d in cell M1, and you will have to select column L, drag the column over with the fill handle to column M and you will see all the corresponding "Options" of all instances of d in Column A.

The way it works is that it has this formula =IFERROR(INDEX($B$2:$B$200, SMALL(IF(J$1=$A$2:$A$200, ROW($A$2:$A$200)-ROW($A$2)+1), ROW(1:1))),"") in cell J2. When you enter this formula in J2 and press CTRL+Shift+Enter, this ctrl+shift+enter combination creates an array formulae. It returns an array. If you just hit enter, it simply gives you an error or un-expected results. You can just drag the formula down to have see the whole return value. If you've dragged the formula down more than the number of results, the IFERROR function catches it and returns just an empty string instead of the error, so that you won't have errors in your dropdown and instead, you will have some blank options. The dropdown just uses a basic OFFSET function which looks like this OFFSET($I$2,0,MATCH(D2,$J$1:$Z$1,0),10,1). This offset function also returns an array. It just searches the value of cell D2 in the first row from J1 through Z1. If D2 has the string c, it searches for c in J1:Z1 and when it finds it, it gets how far it is from I2, and moves that many columns over, and selects 10 cells below it. These 10 cells are the things you get in the dropdown. If you have a lot of data, you might want to increase the number 10 to a bigger number and you might want to change J1:Z1 into something like J1:ZZ1.

Any problems regarding this file or the formulas, you can just comment and I'll try my best to solve them.