Need help referencing the results of my query

[fischera9850]

Hello,

I am the commissioner of a fantasy football league and we need to track some pieces of data that ESPN doesn't. One of those is the team with the weekly high score.

In the attached file, you'll notice I have different tabs for each week of the fantasy football season "Week 1", "Week 2", etc. In each week's tab I have a function to determine which team scored the most points that week (Cell B19 in the "Week 1" tab). If there is a tie it will return all the teams with the top score.

My question is how can I reference these high scores winners in my "Overview" tab?

In the "Overview" tab, you'll notice that I have tables for each team. In each table I have a column under each team titled "High Scores" (Columns: B, H, N, Z, etc.).

I would like those columns to reflect the teams that get high scores each week. If a team had the highest score I want the cell to display "Yes" and if the team didn't a high score it should say "No".

For example, if you use the data that is currently in my "Week 1" tab, cells B3, H3, N3, Z3, AF3, AL3, and BD3 should read "No" because those teams didn't have the highest score. Cells AR3 and AX3 should read "Yes" because they did have the highest score.

Here's my current equation in cell B3 in the "Overview" tab: =iferror(if(indirect("'"&AQ3&"'!$B$19") = $AQ$1,"Yes","No"),"TBD"). It's referencing the result of my high score function in cell B19 in the "Week 1" tab and if the result matches the team name then I record "Yes" and if it doesn't it records "No". I need to tweak this so it can display "Yes" for more than one team if there is a tie for the high score.

Thanks in advance for your help!

[HansDouwe]

Did I understand correctly that you ask for formulas for cells B58 to B62 on the overview sheet?
I don't quite understand the calculation yet. Can you explain exactly how you arrived at those results?

[fischera9850]

I need to adjust the formula in cell B3 on the "Overview" tab. The formula in B3 right now works fine, except when there are ties. Here's the current formula:

=iferror(if(indirect("'"&A3&"'!$B$19") = $A$1,"Yes","No"),"TBD")

First, The formula looks at B19 in the Week 1 tab.
If the team who got the high score matches the team name in cell A1 in the "Overview" tab then it populates B3 with "Yes", if they don't match then it puts "No" in B3.

This league needs to track certain pieces of data that ESPN can't. One of those is weekly high scores.

Each week I copy and paste our fantasy football matchup data. I want this excel sheet to automatically record who got the high score each week because they win money.

[HansDouwe]

Try this formula

Please Login or Register  to view this content.

This formula looks if there is anyone in B20.
If so the result would be "TBD".

Is this to your liking?

[fischera9850]

That statement will not work. However, you pointed my in the right direction. After editing it, i think it works how I want it to now.

You statement actually returns an error, but it was caught in my iferror statement. Here is what I tweaked it to be:

=IF(INDIRECT("'"&AW3&"'!$B$19")=$AW$1,"Yes",IF(INDIRECT("'"&AW3&"'!$B$20")=$AW$1,"Yes","No"))