data validation: filtering a dynamic list

[sfister]

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

 

[sfister]

again thanks for the help.... still can't get it to work, i am beyond frustrated and feel like an idiot to think i apparently can't cut and paste or push three buttons at same time to get your work to work. so i will just settle for a list of all athletes..

 

[Aladin Akyurek]

again thanks for the help.... still can't get it to work, i am beyond frustrated and feel like an idiot to think i apparently can't cut and paste or push three buttons at same time to get your work to work. so i will just settle for a list of all athletes..

Why don't you copy the formula from the file?

Are the Yes values 3-letter entries?

 

[sfister]

I have tried coping from the file as well, same result. Yes is 3 letter entry. your files works as planned. but once in my sheet, no good. maybe I don't understand where when and/or how to CSE then copy down... it seem straight forward.

 

[sfister]

interestingly though, I can paste formula from my sheet to yours and it works in yours...I decided to put "help" in the value_if_error and sure enough, my cells fill with help.

 

[Aladin Akyurek]

I have tried coping from the file as well, same result. Yes is 3 letter entry. your files works as planned. but once in my sheet, no good. maybe I don't understand where when and/or how to CSE then copy down... it seem straight forward.

Only the formula of A3 requires CSE. When done, A3 can be copied down.

interestingly though, I can paste formula from my sheet to yours and it works in yours...I decided to put "help" in the value_if_error and sure enough, my cells fill with help.

Where are the names and the yes/no values in your workbook?

Go to Formulas | Name Manager in your workbook.

What names do you see there? Would you copy of each name what the name refers to and paste here?