Need a formula to do the following...

[Guest]

I have a team listed in cells B2:B45. I have tabs for each of these teams.
I want a formula in cells C2:C45 to take the value from cell R2 from the team
listed beside it. For example, cell B2 is listed as BOS. I want cell C2 to
take the value from cell R2 in the BOS tab. I think I use the indirect
formula but I can't get it to work. Thanks for the help.

 

[Jason Morin]

=INDIRECT("'"&B2&"'!R2")

HTH
Jason
Atlanta, GA

 

[Biff]

Hi!

=INDIRECT(B2&"!R2")

Biff

 

[Guest]

I thought that was the formula to use, however, I get a #REF! error when I
use that one. The formula in B2 is

=INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A$1:$A$1300=$A$1,ROW(Schedule!$A$1:$A$1300)),1))

and the formula in R2 is
=SUM(L2:L163)/O2

If I overwrite the formula in B2 and just type in BOS it works perfectly.
Any idea why it is not working? I had this setup with NHL teams and just did
a save as and deleted things and inserted baseball teams. Would this have
any effect on it? Does data need to be enter in in a certain order? Thanks
for the help.

 

[Guest]

nevermind, I put the trim command before the index and it works now. Thanks
for all the help.