|I want to have a formula that shows will display a data validation list only if certain text is displayed in another cell and to show as N/A if it is not. I tried the solution below that I found on this site but when I select the drop down list it shows the formula itself as the drop down list rather than the values. Not sure what I'm doing wrong|
In Cell G2 enter Plasma (no quotes around it and no equal sign)
In cells H2 to Hx enter a list of allowable Plasma values
Select the cells H2 to the last Plasma value entry in column H, and in the address box (the area that shows the cell address just above the A of the first column, replace the single cell address with Plasma (no quotes around it), then hit Enter while the cursor is still in the box.
In cell I2 enter No selection
In cell A1 add data validation as a List and enter: =$G$2:$G$3 in the 'Source:' box
In cell B1 add data validation as a List and enter: =IF($A$1="Plasma",INDIRECT($A$1),I2) in the 'Source:' box
Now when Plasma is selected in cell A1, cell B1 will have a drop-down list containing the list of available Plasma values.
If A1 is blank or a value other than Plasma, the drop-down in B1 will show "No selection"
This works by the data validation in B1 using the formula:
IF($A$1="Plasma",INDIRECT($A$1),I2) - so if A1 contains the word Plasma it uses the INDIRECT function to return the range named Plasma, which is the list of values in column H. If the value in A1 is not Plasma, the text in cell I2 is used for validation - in this case the text "No selection".