Forum Discussion
How do I reference data from one table in a different table?
An example workbook to play with would be nice.
My thoughts at this stage are that, in order to make the formula robust against sorting the source data, the scores must be looked up by key and not by location. Using a dynamic array version of Excel, my suggested formula would be
= SUM( XLOOKUP( Team["@"[PlayerA]:[PlayerB]], Individual[Player], Individual[Score] ) )
where:
Team["@"[PlayerA]:[PlayerB]] are the players that comprise the current team;
XLOOKUP returns a score for each;
SUM provides a team score.
1. I could well have got the wrong end of the stick.
2. Would need changes to work without DA
3. The quotes around the "@" symbol are only used to display here
PeterBartholomew1 Here's the Spreadsheet I'm working on at the moment (mind the comical names, they're aliases):
Player Stats
Team StatsI've gone ahead and colour coded each player to the team they belong to (i.e. Meerteo and Wingus are both red, as they're on team Meerteo Brudders, who are also filled in with red). Essentially, I want the sum of Meerteo's K value and Wingus' K value to appear under Team Meerteo Brudder's K value, if possible. Ideally, I'd like to be able to sort through each statistic on both team stats and player stats without breaking either table as well.
Your colourfully named friends present no problem. What is a problem is that there is nothing other than the wild markup colours to indicate that Meerteo and Wingus are on team Meerteo Brudders.
Players could be listed within fields of the team table or, alternatively, they could be assigned to a team on the Player table. The first option would require adapting my formula from the prior post, the other option would require the SUMIFS formula to pick out the relevant scores.
= SUMIFS( Individual[Score], Individual[Team], [@Team] )
This approach is the simpler.
