function to use sheet's name as variable

[Guest]

Hi,

I have a worksheet with contracts. For each contract I have a sheet which
has as name the client's code. There are lots of sheets. In each sheet the
structure is the same, so, for example in cell D9 in every sheet you'll find
the same info regarding that client
In another sheet, I want to put in a cell the client's code and to extract
the info I want from the client's sheet. So I want to use the sheet's name as
variable.
Is there a way I can do that with a function ?

Thx

 

[JE McGimpsey]

One way:

A1: <client code>

D1: =INDIRECT("'" & A1 & "'!D9")

 

[Guest]

=MID(CELL("filename"),(FIND("]",CELL("filename"))+1),50)

This will extract the name of the sheet.

 

[Guest]

Thanks guys ...

it worked