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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,894
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,894
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:


Excel 2010
ABCDE
1Group AssignmentZone AccessGroup AssignmentZone Access
2Security Group BZone 2Security Group BZone 2
3Security Group BZone 4Zone 4
4Security Group BZone 6Zone 6
5IT Group AZone A
6IT Group AZone B
Dashboard
Cell Formulas
RangeFormula
E2{=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$D$2,ROW($A$1:$A$6)),ROW(1:1)),2)),"",INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$D$2,ROW($A$1:$A$6)),ROW(1:1)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.


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,894
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,836
Messages
5,833,903
Members
430,241
Latest member
Matty Se

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