Relative referencing of worksheet

[paradox]

My search for this has not gone well...

I have a list of worksheet names within the book I am working with.
I need to point to any one of the names on the list and use it to find the value of a cell within the selected worksheet.

Please assume the name of the worksheet is "Harry Smith" located in cell B27 on the active sheet and the information I need on the selected sheet is found in cell E12.

What does the formula look like?

Thanks in advance!

[davesexcel]

A UDF could help,
This is explained quite well here

http://www.ozgrid.com/VBA/VlookupAllSheets.htm

[paradox]

Thanks for the link. That is a very useful piece of code.

However, what I need to do is take the name of the worksheet such as "Harry Smith" and use in in a formula to obtain the value of a cell in "Harry Smith" at B27.

The name "Harry Smith" is obtained via a dropdown list that puts "Harry Smith" into cell in the current worksheet at, say, C45.

"Harry Smith" will change each time but the formula needs to be the same for all names selected. Something like $C$45 ?

Thanks in advance.

[all4excel]

Thanks for the link. That is a very useful piece of code.

However, what I need to do is take the name of the worksheet such as "Harry Smith" and use in in a formula to obtain the value of a cell in "Harry Smith" at B27.

The name "Harry Smith" is obtained via a dropdown list that puts "Harry Smith" into cell in the current worksheet at, say, C45.

"Harry Smith" will change each time but the formula needs to be the same for all names selected. Something like $C$45 ?

Thanks in advance.

Paradox,

You can create list of all the TAb names in a column and then use it s dropdown.

If i understand u correctly what u want is to get the data from a constant cell B27 from any of the selected sheets/tabs in your Drop-down


Lets say u store all the names of all the tabs in a column m from m2 till m20
U have a dropdown in cell b2, then in c2 type this formula
u can use this formula in cell c2-

=INDIRECT("'"&$B$2&"'!$C$45")

Pls note that there 2 quotes 1 single quote again double quotes & $B$2 and 2 quotes 1 single quote exclamation..

You can even make this cell ($C$45) address dynamic by storing it somewhere and refering the cell which stores...

Hope this takes care of your question...

[paradox]

Thank you! Your formula did the trick. Much appreciated!

[all4excel]

Thank you! Your formula did the trick. Much appreciated!

I'm glad to be of help to you.