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

As the source for the drop down list use:

=INDIRECT(D2&"!C4:C27")

You're welcome. Thanks for the feedback!

Just need a minor tweak...

=INDIRECT("'"&D2&"'!C4:C27")

That may be hard to decipher with all those quotes. Here it is in "exploded view"...

=INDIRECT( " ' " & D2 & " ' ! C4:C27 " )

Good deal. Thanks for the feedback!