Relative referencing of worksheet

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.

Originally Posted by 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.

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...

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

Originally Posted by paradox

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

I'm glad to be of help to you.