I have an Excel spreadsheet that I created drop down lists on. I am creating
dependent lists and hiding previously used items. I can get both of these
options to work independently, but I can't get the Data Validation for these
cells to work with both together. I created Defined Names for both of them,
but I've tried to get both of them in the Source field together with no luck.
Is there a way to make both of these options work together?

Defined Name "PlayerCheck" (This removes the selected players from the drop
down list) =OFFSET(Players!$C$1,0,0,COUNTA(Players!$C$1:$C$6)-COUNTBLANK
(Players!$C$1:$C$6),1)

Defined Name "PosFilter" (This filters the drop down based on another drop
down selection in another column)
=INDIRECT(A2)

I've tried putting them both in the Source field of the Data Validation, but
the the drop down stops working. Any suggestions?

If you're using dynamic lists, you may need to incorporate the technique
shown he

http://www.contextures.com/xlDataVal02.html#Dynamic

Jerkyboy wrote:
I have an Excel spreadsheet that I created drop down lists on. I am creating
dependent lists and hiding previously used items. I can get both of these
options to work independently, but I can't get the Data Validation for these
cells to work with both together. I created Defined Names for both of them,
but I've tried to get both of them in the Source field together with no luck.
Is there a way to make both of these options work together?

Defined Name "PlayerCheck" (This removes the selected players from the drop
down list) =OFFSET(Players!$C$1,0,0,COUNTA(Players!$C$1:$C$6)-COUNTBLANK
(Players!$C$1:$C$6),1)

Defined Name "PosFilter" (This filters the drop down based on another drop
down selection in another column)
=INDIRECT(A2)

I've tried putting them both in the Source field of the Data Validation, but
the the drop down stops working. Any suggestions?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html