data validation: filtering a dynamic list

sfister

New Member
Joined
Oct 11, 2014
Messages
8
hi

I am hs coach building a workout planner in excel 2010, where I have an Athletes sheet and a workout sheet. On the workout sheet I am using dynamic list to select an athlete name and pull other data in via index and match. Currently all athletes show up in the list. I want to only have "active" athletes In that list. I do have an Active column on the athletes sheet.

Currently the list is set as: =OFFSET(Athletes!$B$1,1,0,COUNTA(Athletes!$B:$B)-1,1)

how can I filter out only the active athlete. Active column is D

thanks in advance

scott
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
no duplicates athletes in table. column headers are as follows: ID, name, gender, active, position, bodyweight, squat, bench, clean, snatch.

active column is validated with yes and no only

Create a new sheet, named Admin.

Define Lrow using Formulas | Name Manager as referring to:
Rich (BB code):
=MATCH(REPT("z",255),Athletes!$B:$B)

Activate Admin...

A1: Active

A2, Active list

A3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX(Athletes!$B$1:INDEX(Athletes!$B:$B,Lrow),
  SMALL(IF(Athletes!$D$1:INDEX(Athletes!$D:$D,Lrow)="Yes",
  ROW(Athletes!$B$1:INDEX(Athletes!$B:$B,Lrow))-ROW(Athletes!$B$1)+1),
  ROWS($A$3:A3))),"")

Define now ActiveList using Formulas | Name Manager as referring to:
Rich (BB code):
=Admin!$A$3:INDEX(Admin!$A:$A,MATCH("*",Admin!$A:$A,-1))

The definition ActiveList can be used in formulas and as Source in a data validated cell. Note though that the list is not extracted in alphabetical order, rather as is.
 
Upvote 0
i must have done something incorrect. I defined Lrow as stated, then created admin sheet. in a1 typed "active", in a2 typed "Active list" in A3 copied and pasted the above expression: I only got a blank cell with formula in it
 
Upvote 0
i must have done something incorrect. I defined Lrow as stated, then created admin sheet. in a1 typed "active", in a2 typed "Active list" in A3 copied and pasted the above expression: I only got a blank cell with formula in it

Did you apply control+shift+enter (CSE) to the formula of A3? CSE means: Press down the control and the shift keys at the same time, while you hit the enter key.
 
Upvote 0
yep. I cut and paste expression then pressed CSE (got the {} around formula), then pulled down the corner all the way to A255 and let go. formula is in all cells but no names appear. am i correct to expect the names of athletes from Athlete!B:B with "yes" for active to appear?
 
Last edited:
Upvote 0
Upvote 0
thanks... the only difference I see is my athletes table has headers, but other than that, my sheets look like yours, same formulas and I still don't get your results..... thanks for the help, I don't understand why
 
Upvote 0
thanks... the only difference I see is my athletes table has headers, but other than that, my sheets look like yours, same formulas and I still don't get your results..... thanks for the help, I don't understand why

Headers should not affect the set up. See the file with headers included.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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