Drop-Down Help

Blob Eye

New Member
Joined
Jun 5, 2015
Messages
8
Hey Everyone!

I already know how to create a drop-down list, but I want to go a little deeper and have my selection from that list control the data in the next column over. Is this possible? Thank you.


Group AssignmentZone Access
Security Group AZone 1
Zone 2
Zone 3

<tbody>
</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hey Everyone!

I already know how to create a drop-down list, but I want to go a little deeper and have my selection from that list control the data in the next column over. Is this possible? Thank you.


Group AssignmentZone Access
Security Group AZone 1
Zone 2
Zone 3

<tbody>
</tbody>
Probably, but we can't help you unless you provide some details about what you want to happen in the next column when the drop down selection changes, where the drop down is located, ....
 

Blob Eye

New Member
Joined
Jun 5, 2015
Messages
8
So basically, the example I gave is the output I'd expect in column B if I chose "Security Group A" from a drop-down list in column A.

If I selected "Security Group B" from column A, I want the data in column B to changed to the appropriate zone accesses needed. Does this make sense?

Example B:

Group AssignmentZone Access
Security Group BZone 2
Zone 4
Zone 6

<tbody>
</tbody>
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
So basically, the example I gave is the output I'd expect in column B if I chose "Security Group A" from a drop-down list in column A.

If I selected "Security Group B" from column A, I want the data in column B to changed to the appropriate zone accesses needed. Does this make sense?

Example B:

Group AssignmentZone Access
Security Group BZone 2
Zone 4
Zone 6

<tbody>
</tbody>
Good start, but how does one determine which zones correspond to each Security Group? Are there always three and only three zones for each possible Group?
 

Blob Eye

New Member
Joined
Jun 5, 2015
Messages
8

ADVERTISEMENT

No, these are just examples as I didn't know how much information you actually needed. The number of zones will be determined by a user's particular "Group" access needs. A basic user will have a minimum of 3 zones for building access. However, for security and IT personnel, they can have as many as 15 zones assigned to them.
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
How about something like this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Group Assignment</td><td style=";">Zone Access</td><td style="text-align: right;;"></td><td style=";">Group Assignment</td><td style=";">Zone Access</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Security Group B</td><td style=";">Zone 2</td><td style="text-align: right;;"></td><td style=";">Security Group B</td><td style=";">Zone 2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Security Group B</td><td style=";">Zone 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Zone 4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Security Group B</td><td style=";">Zone 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Zone 6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">IT Group A</td><td style=";">Zone A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">IT Group A</td><td style=";">Zone B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Dashboard</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">INDEX(<font color="Green">$A$1:$B$6,SMALL(<font color="Purple">IF(<font color="Teal">$A$1:$A$6=$D$2,ROW(<font color="#FF00FF">$A$1:$A$6</font>)</font>),ROW(<font color="Teal">1:1</font>)</font>),2</font>)</font>),"",INDEX(<font color="Red">$A$1:$B$6,SMALL(<font color="Green">IF(<font color="Purple">$A$1:$A$6=$D$2,ROW(<font color="Teal">$A$1:$A$6</font>)</font>),ROW(<font color="Purple">1:1</font>)</font>),2</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Drag the formula down as far as you need.

Source: How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

No, these are just examples as I didn't know how much information you actually needed. The number of zones will be determined by a user's particular "Group" access needs. A basic user will have a minimum of 3 zones for building access. However, for security and IT personnel, they can have as many as 15 zones assigned to them.
To cut to the chase, only you have knowledge of what your workbook/worksheets look like and what you want us to help you with. You need to provide sufficient detail or we can't help. Take a look at some of the posts that have achieved a solution for the person posting, that might help to understand the type of details required.
 

Blob Eye

New Member
Joined
Jun 5, 2015
Messages
8
First of all, thank you all for your help. But maybe it would be better explained like this...

Let's say Cell A2 is a drop-down list of multiple cake recipes. What I'm looking for is when I select one of the recipes from A2, I need column B to show all the ingredients needed for that recipe. That's the simplest way I can explain it. The only thing that should be in column A at this point is the column title in A1, and the drop-down list in A2. Column B will vary depending on the selection from cell A2. Does this clear it up?

Thank you,

Phil
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
First of all, thank you all for your help. But maybe it would be better explained like this...

Let's say Cell A2 is a drop-down list of multiple cake recipes. What I'm looking for is when I select one of the recipes from A2, I need column B to show all the ingredients needed for that recipe. That's the simplest way I can explain it. The only thing that should be in column A at this point is the column title in A1, and the drop-down list in A2. Column B will vary depending on the selection from cell A2. Does this clear it up?

Thank you,

Phil

You will have to have your data somewhere to base your formula off of. In my example above, that data is in columns A and B but you can put yours anywhere. My formula does exactly what you are explaining.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,557
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top