IF Function in Data Validation with Sources from other Worksheets

**pandaman** · 12-16-2010, 11:30 PM

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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!

**JBeaucaire** · 12-16-2010, 11:54 PM

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)

**pandaman** · 12-17-2010, 12:36 AM

Works perfectly, thanks JB!

Unfortunately now I have a new question.

Consider this, I have two dropdown boxes in B3 and D3. B3 has the choices "Test" and "Asdf" available.

D3 on the other hand will have two different sets of choices available to the User depending on what value is currently in B3. If B3 contains "Test", then your choices in D3 are "Test 1" and "Test 2". If B3 contains "Asdf", then your choices in D3 are "Asdf 3" and "Asdf 4".

I've attached a sample .xls file to demonstrate if my explanation isn't sufficient.

My problem with this, is that if the User has chooses either "Test 1" or "Test 2" in D3, then later changes B3 to "Asdf", D3 will still display the Test data even though it shouldn't be available to the User. Granted, if the User then clicks the dropdown in D3, only the Asdf data will be available as it should, but the cell itself will still contain the data "leftover" from the previous set.

Is there a method to clear/delete the D3 cell whenever the B3 cell changes values? I can't seem to find any functions that involve deleting information. I've Googled this, and while it seems that I can achieve this via Macros in VBA, I was wondering if there was a simpler method.

**ChemistB** · 12-17-2010, 11:32 AM

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

Please Login or Register  to view this content.

Does that work for you?

**JBeaucaire** · 12-17-2010, 12:33 PM

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