Excel 2007 : index - getting sheet name from a cell value

[cyph]

hey. i want to make a formule using index, which will get value from sheet. it wouldnt be a problem, but i have a lot of sheets, all of them called by names and i want formule to get sheet name from a cell. sth like :

Please Login or Register  to view this content.

i dont know how to get name from cell, for example a60. "a60" doesnt work, is it possible?

[DonkeyOte]

Your range doesn't make sense (single cell - AD17) but in theory:

=INDEKS(ADR.POSR("'"&A60&"'!$AD17:$AZ17");$H$1)

[cyph]

hm, why it doesnt make sense? maybe its not range that i want, i just want single value from a cell, but when i used it in my purpose it gives what i want. sth is wrong with Your formule, it gives me #name?. My works like i want, but dont know how to use Yours adr.posr..

[DonkeyOte]

I assumed (incorrectly it seems) that you were using a Polish version of Excel - ADR.POSR being the Polish equiv. of INDIRECT.... I don't know the equivalent for your version (Croatian or other ?)

If you're retrieving only a single value what purpose does H1 have in your INDEX call ?

[cyph]

You assumed well, it is polish ver, so it should be ok. hm, i messed up sth, actually i dont know what H1 had to mean.. okay, so I want a formule, which will get name from cell and use it as a sheet name and from that sheet get value from AD17.

[DonkeyOte]

So assuming the value in A60 is a valid sheet name what does:

=ADR.POSR("'"&A60&"'!$AD17")

return ?

[cyph]

yeah, now its fine, thank You very much for help DonkeyOte