2 level cascading drop down menu

**josanba** · 08-15-2012, 09:17 AM

Dear all

I have been trying to make up my mind trying to design a simple 2 level cascading drop down menu. In the process, I have had a look at past posts and suggested links (e.g. data validation on contextures website), however I am slightly thick headed and would still need some kind help to point towards the right direction.

Essentially, I am trying to convert the contents of the following table in 2 pull down menus:

kV	50 kV	80 kV	100 kV	120 kV	150 kV
Filter	1	2	4	5	6
Applicator diameter [cm]
3 circle	108.8	113.1	118.4	120.1	125.2
4 circle	105.9	109.3	113.7	114.5	118.3
5 circle	104.1	106.8	110.4	110.8	113.6
10 circle	100.0	100.0	100.0	100.0	100.0

Pull down menu 1: Filter selection
Pull down menu 2: Applicator diameter

The result of the combination of these two selections should return the user the corresponding numeric value as indicated in the table.

I am failing to design the way of the two-variable selection which will give the numeric value to the user. Would anyone kindly advise me to use data list validation or use a macro instead?

Any help would be much appreciated

Thanks in advance

**Richard Buttrey** · 08-15-2012, 09:39 AM

Hi,

Does the attached help?

**josanba** · 08-15-2012, 10:09 AM

Thanks very much!; honestly, it works beautifully.

I am curious about the syntax of the MATCH function you have used:
lookup_value: value we want to match in array (from the pull down menu) i.e. "A2" or "A5": OK
lookup_array: range of cells containing the look up value (selected in the table) i.e. "kv" or "diam": OK
match_type: how excel matches lookup_value with lookup_array: I don't fully understand the values you have given of "...FALSE)+1" and "... FALSE)+3"

Out of interest, would you be so kind to briefly explain how you have used these arguments?
Thanks again

**watersev** · 08-15-2012, 10:54 AM

hi josanba, different option with 2 functions only

**Marcol** · 08-15-2012, 11:11 AM

I read your needs differently, the workbook is done so I'll post it as a possible alternative.

Select from the yellow cells.
Previous selections are cleared using the Worksheet Change Event

**Richard Buttrey** · 08-15-2012, 01:10 PM

Hi,

I'm unclear. You have essentially two interpretations of your request. Mine (& the similar from watersev) and Marcol.

Which, if either, are giving you what you want?

Perhaps you could give some examples of what you expect to see in the drop downs (I don't for instance see A2, A5 etc just 1,2,3 4,5), and given those selections the results.

The MATCH function is commonly used to find the position of one value in a list of values. It's often coupled with an INDEX function since once you have the positions of a couple of values in a row or column of a table, you can use that position in the Index function to find the value at the intersection in the table.

I specified +1 for the column since the table (range name tbl_data) is in E1:J7. I happened to have specified the kv range name as being F1:J1 hence column 1 in this range is column 2 in the tbl_data range. Hence the need to add 1.

Similarly with the +3. The range name diam covers E4:E7. Hence the value in say E5 is the second row in the table, but in the tbl_data range it is the 5th row, hence the need to add 3.

There's no particularly correct way. And often I prefer to use the position in the whole sheet when specifying Matches and Indexes. The advantage of that approach is that you then don't need to worry about the different positions of the ranges, which was the case here.