Excel "Substitution" 1

[Scott24x7]

Hi all,
I have a question. Is it possible to value or macro substitution in Excel? So as example, I have a field in Sheet1 called "Names"

It contains:

Bob
Scott
Jim
Jill

These names also match the name of corresponding sheets. In column C what I'd like to do is something like:
=Bob!A5 and have that populate with the value from At of Bob sheet.

But, what I want to be able to do is use the value from column B to "build" the expression.

So I imagine something like =B3&'!A5' and get the value from the sheet. But "B3" doesn't get created in the expression as the substituted value, but instead as "B3&'!A5'" which is just gibberish.

Any way to do what I'm talking about here?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

hi,

You can use the INDIRECT() function to concatenate a STRING and then 'convert' the string to a reference like...
[tt]
=INDIRECT(B3&"!A5")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Thanks. I found another problem when using this in another field. I also have some fields that are of mixed langauge type, such as:


苏州 Suzhou
杭州 Hangzhou

But when I do something like:

=INDIRECT(C3&"!E5")

(Where C3 is mixed type above), I get #REF! as the result. Is there some what to fix that? (Tabs have same value).



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

If your name has spaces
[tt]
=INDIRECT("'"&C3&"'!E5")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Ha! Awesome. Cheers, many thanks.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

In fact, you can use that type of forumla to accomodate spaces, on ALL your names.

"irreducible complexity" ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}