Dropdown menu entries that compare the contents of multiple lists

**Cres** · 07-29-2012, 03:06 PM

First of all, thanks in advance for the help. It's my first time coming here. I'm not even sure if this is the right place to post. Hope I'm not breaking any excel forum rules or anything. I browsed around for a while first but couldn't find the information I was looking for.

Here's the breakdown of what I want to do:
I want two drop-down menus that offer a choice between many entries. Each entry refers to a list. Somehow, the spreadsheet returns to the user the differences between the two lists selected.
I can probably figure out how to use VLOOKUP on my own, and creating the drop down menus is simple enough. It's having each possible value of the drop down menu refer to a different list that has me stumped.

To put this in a bit of context, the idea is to compare TV packages for a local TV provider, in terms of the channels included. Ultimately the goal is to have the user pick the "before" and the "after" package, and get the difference between the two.

I was thinking of doing this by having each package as a list of every single channel possible, with each channel having a value of either 0 or 1 in a second column. That way I could (ideally) have some sort of elaborate IF, THEN statement with 4 possibilities, 0,0 0,1 1,1 and 1,0 or (if that fails) I could just substract one list from the other outright, but this is an inferior option because it wouldn't differentiate between two packages that retain a channel and two packages that both don't have a channel.

Now I have drop down menus that refer to a list of the possible packages, but I can't seem to figure out how to have the different possible drop down selections refer to the lists of channels in question, much less how to manipulate those lists to generate the final list to return to the user. If anyone could help me with that, it'd be enormously appreciated.

**Pepe Le Mokko** · 07-29-2012, 03:13 PM

Hi

does this get you started?

**Cres** · 07-29-2012, 03:47 PM

Maybe I'm just not understanding that tutorial page correctly, but it seems to me like what it's expressing is the ability to have the choices of a second drop down menu be defined by the selection made in the first drop down menu. In my case, the options available are not dependent; both drop down menus would simply display the entire list of TV packages available. My challenge is having excel define whatever you select in the drop down menu as refering to a specific list of values elsewhere in the document, so that we may then compare those lists of values corresponding to the two drop-down choices the user made. I'm gonna write this out in an example to make this clearer.

Let's say you have 3 possible TV packages called um... Basic, Medium and Premium. There exist only 3 channels in the world, let's say, CNN, Fox and Discovery. Premium has all 3. Medium doesn't get discovery. Basic only gets Fox.

So somewhere in my document would be the following lists:

Basic   Medium   Premium
0       0        1
0       1        1
1       1        1

So if a user selects "Basic" in the first drop down menu and "Premium" in the 2nd, I want the document to spit back:

1
1
0

Meaning, the package now contains +1 Discovery, +1 CNN and the same number of Fox. I can handle it from there, associating the correct user-friendly designations and so on, but this part of the exercise really stumps me.