Solved Excel If statement & Data Validation

Microsoft Microsoft excel 2007 full vers...
August 2, 2010 at 02:29:55
Specs: Windows 7
I am trying to do an if statement to include a data validation list. So if cell A1 says "Plasma" then in cell B1 show a drop down list of the size of plasma's. If A1 says anything else then I don't want the drop down list to show at all because they will need to ensure some other text. Can anyone help?

See More: Excel If statement & Data Validation

August 2, 2010 at 04:08:02
✔ Best Answer

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".


Report •

August 2, 2010 at 05:25:04
To add on to Humar's suggestion, I assume your users have more than one item that might be entered into A1.

For that you could also use a Data Validation Drop Down in A1, using a list of all of your Named Ranges. This will force them to choose from a list and not enter items that won't populate the B1 list.

Whichever Named Range (Plasma, etc.) is selected from the A1 Drop Down will populate the Drop Down in B1 with the corresponding items.

For that, you would simply use =INDIRECT($A$1) as the List Source for the B1 Drop Down.

The Drop Down in B1 known as a Dependent Drop Down.

Report •

March 14, 2011 at 07:32:16
Thank you soooo much for the big help that you provide, i was looking for this solution 4 weeks back, and it works excellent.

Report •
Related Solutions

Ask Question