IF Formula and Validation List (Excel 2007)

**NoraJohnson** · 10-22-2009, 02:46 PM

I apologize if the answer's already been given in another thread, but I couldn't quite find it in my search.

In a single cell, I'd like to include an IF statement and validation list reference. The goal: if a cell is a certain value, I'd like all of the following cells in that record (row) to kick over to "N/A". These other cells currently reference a validation list. Otherwise, I'd like the drop-down lists to be available.

My thought was: =IF(a2="Support","N/A",...)
For the ... false value placeholder, I was hoping to be able to reference the validation list.

The error I'm getting when I try the IF statement in a validation list cell is: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Is there an easy fix for what I'm trying to achieve?

**NBVC** · 10-22-2009, 03:00 PM

Try this...

It won't put N/A in the cell, but will not allow a dropdown list to show....

Make sure A2 is blank for now...

Go to Data Validation and choose List from allow menu, then

enter formula:

=IF(A2="Support","",List)

where List is the name of your named list.

Click Ok.

Now type Support in A2 and you should not be able to get a list in your data val. cell.

If you select other things, you will get the list.

**NoraJohnson** · 10-22-2009, 03:54 PM

Thank you, NBVC. I really appreciate your quick turnaround, and your recommnedation did work as you said it would.

My primary challenge with leaving the cell blank versus kicking it over to "N/A" is that with an "N/A" tag, people know there is not supposed to be a data value there, whereas a blank cell will cause people to ask if the user simply failed to update their data like they were supposed to. Technically, the manager could look to see if "support" is present, indicating there should be no values in those cells, but that doesn't really happen. Also, the team could easily select NA from the dropdown lists I created for them, but they were hoping I could save them from that work (~10 cells for each record).

Thanks again!

**NBVC** · 10-22-2009, 04:04 PM

How about making the cell black if "Support" is in A2

So apply Conditional Formatting from Home tab to the cell...

Select New Rule and select Use a formula to determine which cells to format...

then enter: =$A$2="Support"

Click Format and choose Black from the Fill tab.

Click Ok and Ok again to finish.

Now, the cell will be black if "Support" is in A2.

Note: Even if they try to type in the box, the should get an error message....