Thread: IF Function in Data Validation with Sources from other Worksheets

Greetings,


What I'm trying to create is a In-cell dropdown box that shows different options depending on the value of a different cell.

My method of creating this conditional Data Validation is to use the IF function:

=IF(A1="Test",Worksheet2!$D$15:$D$16,Worksheet2!$D$17:$D$18)

However, since my source cells for the Data Validation is on a different worksheet, I get this error message when I try use the above formula:

"You cannot use references to other worksheets or workbooks for Data Validation criteria."

To double check if that was the only problem, I entered the same formula but referenced cells on the same sheet:

=IF(A1="Test",D$15:$D$16,$D$17:$D$18)

And it works perfectly.

What's interesting though, is that if I don't use the IF function at all, I can reference cells in different worksheets just fine.

So I suppose I have two questions:

1. Why is it I can't use sources from another Worksheet for Data Validation if I'm also incorporating an IF statement, but I can without it?
2. Is there another method for me to achieve my goal? Basically I want different choices to be made available in the dropdown box depending on what the value is in a different cell.

I'm using Excel 2007.

Thank you!

For this to work, you will need to "name" the ranges on the other sheet.

On sheet2, highlight the first range of cells (D15:D16) and the type a name for this range into the Name Box. This box is usually just to the left of the formula bar and typically displays the currently selected cell's address. In that box, type a name like List1.

Highlight the second range (D17:D18) and then name that List2.

Back on your sheet1, open the data validation settings, select:

Allow: List
Source: =IF($A$1="Test", List1, List2)

The only way is with VBA.
Right click on your worksheet tab and "View Code"
Paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        Range("D3").Value = ""
    End If
  
End Sub

Does that work for you?

Originally Posted by pandaman

Is there a method to clear/delete the D3 cell whenever the B3 cell changes values?

No, you can't actually clear a cell, make a physical change to the cell's contents, without using VBA. Which you can do, see ChemistB's suggestion above.

I like to use conditional formatting in this situation to cause the previous D3 selection to hide it's current value if it no longer matches the drop down available based on the B3 selection. I would use this conditional formatting on cell D3:

Formula Is: =NOT(ISNUMBER(MATCH(D3, INDIRECT(IF(B3="Test", "One", IF(B3="Asdf", "Two", ""))), 0)))
Format... Font: White