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:
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
To double check if that was the only problem, I entered the same formula but referenced cells on
the same sheet:
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.