Data validqation using named range and fixed values

[Jake Collins]

I would like for my data validation list to comprise both the values in a named range (called Names) and some fixed values ("role1","role2" and "role3").

I manage a conditional formatting check using

=IF(AND((COUNTIF(Names,B2)+IF(OR(B2="Role1",B2="Role2",B2="Role3"),1,0)=0,B2<>""),1,0)

Is there a way to get the data validation list to include the fixed values?

 

[p45cal]

Try:
=OR(COUNTIF(Names,B2)>0,B2="Role1",B2="Role2",B2="Role3",B2="")
or try the shorter:
=OR(COUNTIF(Names,B2)>0,B2={"Role1","Role2","Role3",""})
but Excel may complain about using an array of constants; it did in Excel 2003.

 

[jeffreyweir]

What's stopping you from simply adding the fixed values to the top of the range that the Data Validation points at?

 

[Jake Collins]

What's stopping you from simply adding the fixed values to the top of the range that the Data Validation points at?

Only aesthetics.