Cell and Sheet Reference/Return

[Guest]

I have 12 Sheets with essentially the same data on each one of them. The
sheets are named by Teams (I.E. Andrew, Will, Shon, Jack). On each sheet I
have the following information in each column (with column headers in the
first row).

A= Week
B= Opponent
C= Team Score
D= Vs. Score
E= Difference
F= W/L/T

What I would like to dois have a formula that would return the Name of the
Team and what week the Highest Score was. I already have the formula for
figuring the Highest Score, but I can't figure out how to make the cell next
to it return the Team or Week. Can someone help me out?

Thanks in advance.

 

[Guest]

What would you do if two teams have the same high score for the week?

 

[Guest]

That's a really good question that I didn't even think about. So far, it
hasn't happened yet, and we've only ever had 2 teams tie each other in any
given week. I guess I'd have to write in some sort of error check to the
formula too. Any ideas for the straight up formula first though?

 

[Guest]

First, I'm assuming that D2 on each sheet is the Team Score for the same
week. If this is not an accurate assumption, this won't work.

To define the Max value from all sheets (in this example, Sheet1:Sheet4, use
this equation:

=Max(Sheet1:Sheet4!D2)

Do a Google Search for 3D Excel formulas for more information. I'm still
cogitating on how you'd get the sheet name for the team with the max score.
You probably could do a COUNTIF to determine if there is more than one team
with a matching high score.

I hope that'll get you started.