Sheet Info From Cell For Data Validation

Hi chaps,

I'm tearing my hair out trying to get this to work, and I fear I'm either missing something obvious, or it can't be done.

I'm using data validation to return a drop down list:

My source is:

=Team7!$C$4:$C$27

where Team7 is another sheet. I've entered this manually - and it works okay, but is there a way for a cell to tell the data validation what sheet to look at, i.e. the content of cell D2 is the sheet to look up... does that make sense?

So in D2 is "Team6" which is calculated from somewhere else, so if it changes, so does the data validation above.

Hopefully I'm making sense!

Thanks