Accessing Different Sheets via a Formula

[Shytott]

Hi
I am currently accessing data from one worksheet to another using:
=INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0))
Ie from my current worksheet ‘Claim’, I’m going specifically to sheet ‘AD’.
My full workbook contains 18 sheets, one for each of 18 engineers and of which, the name of the worksheet is the engineers initials.
What I would like to do, is to use a lookup table located on ‘Claim’ which will hold a list of all 18 Initials, so that when an engineer selects himself from a drop down list, his initials automatically populate cell K7.

My question therefore is, is there a way of substituting the ‘AD’ in the formula: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)) with the contents of K7?
Or, if you can see what Im trying to do here, is there any other way of accessing specific sheets by inserting the sheet name into a formula?

Hope this is not too muddy an explanation, Many Thanks,
Shytott

[NBVC]

Try:

=INDEX(INDIRECT("'"&K7&"'!C:C"),MATCH($B9,INDIRECT("'"&K7&"'!$B:$B"),0))

[Shytott]

Excellent!!
Thanks very much NBVC, thats done the job perfectly.

Can you tell me what INDIRECT is meaning?

Cheers
Shytott

[NBVC]

See excel help on INDIRECT() It explains it well enough...

.. basically as the name implies, it indirectly refers to your sheet via a the name in a cell...

Here are a couple of internet references:

http://www.cpearson.com/excel/indirect.htm

http://www.contextures.com/xlFunctions05.html

[Shytott]

Thanks NBVC, thats very helpful,

Shytott