Dependent validation list

[Andy C.]

I need a Dropdown list that only shows the names that are in an optional contest. The contest is CTP and if a player is entered then in another column it is marked with an "x". In the "Player Entered CTP" box is where I want the Dropdown that only lists those in the CTP contest.

I'm sure it will take a formula in the Validation List but I've researched and researched and can't wrap my mind around on how to do it.

See attached for sample workbook.

Thank you in advance!

Andy

[Estevaoba]

If you can live with a helper column, use this formula, say in K3:

Please Login or Register  to view this content.

Then change the range in your data validation to:
=$K$3#

Good luck!

[wk9128]

Suppose your Data Validation Checklist is in the E3:E20 area to place the code on the active worksheet

Please Login or Register  to view this content.

[menem]

Please review posts in my signature, it's may help.

Regards.

[Andy C.]

No problem on the helper column and it worked GREAT on the sample workbook. But on my active workbook I get a #CALC error. To get rid of the error I added ",0" and now it returns "0". So, that seems to be telling me it's not recognizing the "x"? There are no formulas in columns G or I. Formatting is "General".

Helper column is "AF4"
Name Column is "G4:G39"
CTP column containing "x" is "I4:I39"

Please Login or Register  to view this content.

Andy

[Andy C.]

No problem on the helper column and it worked GREAT on the sample workbook. But on my active workbook I get a #CALC error. To get rid of the error I added ",0" and now it returns "0". So, that seems to be telling me it's not recognizing the "x"? There are no formulas in columns G or I. Formatting is "General".

Helper column is "AF4"
Name Column is "G4:G39"
CTP column containing "x" is "I4:I39"

Please Login or Register  to view this content.

Andy

Okay, so it's working now. I was using a macro to enter the "x" into column "I". The macro had a space "x " that was causing the problem.

[Estevaoba]

Glad it worked.
Thank you for the feedback and for the reputation added.
Have a blessed day!