You are trying to associate a "text value " in a data validation cell with a given numerical value. One supposes that this is so that you can use the numerical value in another formula. It is common practice in forms for each select item to have a description AND a separate "value", unfortunately Google Sheets does not offer this.
The cell that you use for the "IF" formula can NOT be a data validation cell. The data validation cell takes its values ONLY from the reference list/range, and this explains your error message "
Invalid, Input must be an item on specified list.".
However, there are a two alternatives to your complex nested
IFS - "Evaluates multiple conditions and returns a value that corresponds to the first true condition."Doc
=ifs(B1="---",0,B1="Below avg.",100,B1="Average",200,B1="Above avg.",400,B1="High Tier",600,len(B1)=0,0)
=iferror(ifs(B1="---",0,B1="Below avg.",100,B1="Average",200,B1="Above avg.",400,B1="High Tier",600),0)
This formula is typed in anywhere on the sheet; it evaluates the value of cell B1 (the data validation dropdown), and returns a numerical value. I've also considered the scenario where the validation cell hasn't yet been used.
VLOOKUP - Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found. Doc
IFS formula, you use this formula in any cell on the sheet.
I created a helper column beside the data validation list; the formula looks up the list value, and returns the value from the adjacent helper column.
This is my preferred approach - it is much efficient that either the nested
IF or even the
IFS formula since it enables you to easily modify the values without having to edit the formula.
Data Validation cells