Returning a list based on another cell

Irwell

Board Regular
Joined
May 24, 2012
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I've done this before with no problems but can't for the life in me think about how I did it.

In cell A2 I have a drop down list of group names. When I select a group I want A3 to A10 to list the names in that group that are all in another tab.

So if A2 says "Football"

A3 to A10 returns 7 footballers names automatically

But A2 then changes to "Rugby"

A3 to A10 returns 7 rugby players

What do I need to do?

Thanks,
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post the lists that you want the values returned from?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
So this is the page I want it on

Book9
AB
5JobFootballer
6
7
8
9
10NamesStats
11
12
13
14
15
16
17
18
19
Sheet2
Cells with Data Validation
CellAllowCriteria
B5ListFootballer, Rugby Player, Golfer
 
Upvote 0
So this is the page I want it on

Book9
AB
5JobFootballer
6
7
8
9
10NamesStats
11
12
13
14
15
16
17
18
19
Sheet2
Cells with Data Validation
CellAllowCriteria
B5ListFootballer, Rugby Player, Golfer
On the list page that this needs to draw information from....

Book9
ABC
4NameOccupationPoints
5JamesFootballer5
6JohnGolfer10
7JamieRugby Player5
8IanFootballer5
9SimonGolfer50
10LiamRugby Player30
11JamesFootballer33
12JonGolfer20
13KrisRugby Player50
14ChrisFootballer55
15CarlGolfer55
16KarlRugby Player34
17RomeoFootballer20
18RobGolfer93
19RockRugby Player42
20ShaunFootballer54
21SeanGolfer85
List
 
Upvote 0
Hope that makes sense.

So if I select in B5 a golfer from A11 down it returns all the golfers and then I'll have a vlookup in the stats column to pull through the points.
 
Upvote 0
Ok, thanks for that
In A11 put
Excel Formula:
=FILTER(FILTER(List!A5:C21,List!B5:B21=B5),{1,0,1})
 
Upvote 0
Solution
You're a superstar. Thank you. I don't actually think I've ever used that before, normally used index/match etc - I love that. Thank you.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top