List levels 1-4 from drop down menu with custom data validation

[DashboardNovice]

In cell E8, I have a formula for the data validation list. This is what I want to do:

If the Project Type = T&M or FFP/LOE, then show 1,2,3,4 as possible levels to select from, otherwise display "No Billing Rates" in the cell itself or as the only choice in the drop-down list.

Here is my formula:

=IF(OR($E$5="T&M",$E$5="FFP/LOE"),Levels,"")

When I enter "No Billing Rates" in the double quotes as the last argument it doesn't work.

The ideal situation is that if the Project Type = T&M or FFP/LOE, then show 1,2,3,4 from the drop down list, otherwise show "No Billing Rates" as the cell value, instead of appearing in the drop down list.

 

[djc]

Since the formula is used in Data Validation, you need to reference a range in the workbook.

Enter "No Billing Data" into a cell on Sheet2 and define a name for that cell (e.g. NoRates). Then modify the formula as follows:

=IF(OR($E$5="T&M",$E$5="FFP/LOE"),Levels,NoRates)

You can also use Conditional Formatting to highlight an invalid selection when the Project Type changes (see attached).