I am trying to do an if statement to include a data validati

May 28, 2013 at 01:34:17
Specs: Windows 7
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

Try this:
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".

See More: I am trying to do an if statement to include a data validati

Report •

May 28, 2013 at 05:58:24
I tried the steps above and it worked for me.

If I choose Plasma from the drop down in A1, and then click the drop arrow in B1, I see my list of Plasma values from H2:Hx.

If I choose the "blank" selection in A1, the only choice in the B1 drop down is No Selection.

The value in B1 does not change automatically when A1 is changed; for that you would need a macro.

The only way I can see the formula in the drop down is if I leave off the equal sign in the Data Validation source field, which makes the "formula" nothing more than a text string.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question