Not an easy one - sorry!

[stuartk812]

OK I need some help!! I'm (trying) to pull together a file before going away on a golf trip.

I've managed to get to a point where I have an individual score sheet, with scoring boxes for 5 days. I then also have a "team" tab.

Now what I need....

When we're away, we do a draw each night to see whos score yours will be paired with each day. So say on Day 1, even tho Jean has played her round with Tommy, her Day 1 partner on the sheet may be drawn as Kath.

I would like to be able to select a drop down on the team score tab for the partner, and for it to then work out the combined scores of the 2 people for that day.

So, if I select Kath as Jeans partner on Day 1, the sheet then combines Jeans day 1 score with Kaths day 1 score and adds them together. But if were to select Tommy, or Jimmy, it would then do then add Jeans day 1 score to theirs instead.

Is that possible?! Help needed!!

 

[mackc557]

Yes it is possible and should be fairly easy.
What does your Individual Scores sheet look like? I might be able to write up a single formula that you can place in all the scoring sections for your Teams sheet.

 

[stuartk812]

Oh really?! Amazing - thank you.

I've attached an image of what the individual scores looks like.

Yes it is possible and should be fairly easy.
What does your Individual Scores sheet look like? I might be able to write up a single formula that you can place in all the scoring sections for your Teams sheet.

 

[stuartk812]

Oh really?! Amazing - thank you.

I've attached an image of what the individual scores looks like.

These images may be better!

 

[mackc557]

Try this (use some sample data to test if it works correctly):
=IFNA(SUM(INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN(Names))),INDIRECT("'Individual Scores'!B:B"),0),((COLUMN()-COLUMN(Names))/2)+2,,,"Individual Scores")),INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT("'Individual Scores'!B:B"),0),((COLUMN()-COLUMN(Names))/2)+2,,,"Individual Scores"))),"")

Paste this into any of the Day X Points boxes. If there isn't a partner it won't have a value.

 

[stuartk812]

Try this (use some sample data to test if it works correctly):
=IFNA(SUM(INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN(Names))),INDIRECT("'Individual Scores'!B:B"),0),((COLUMN()-COLUMN(Names))/2)+2,,,"Individual Scores")),INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT("'Individual Scores'!B:B"),0),((COLUMN()-COLUMN(Names))/2)+2,,,"Individual Scores"))),"")

Paste this into any of the Day X Points boxes. If there isn't a partner it won't have a value.

I'm getting an "Invalid Name Error" when using this and it's just showing as #NAME?

Is there something I'm doing wrong?

 

[Fluff]

Another option, that doesn't use volatile functions

=SUMPRODUCT(sumifs('Individual Scores'!$C$8:$C$15,'Individual Scores'!$A$8:$A$15,$B5:$C5))

 

[mackc557]

I'm getting an "Invalid Name Error" when using this and it's just showing as #NAME?

Ah! Right! I knew I was forgetting something, the only other thing is to select all the names in the Team Score sheet and name the "Names"

 

[stuartk812]

Ah! Right! I knew I was forgetting something, the only other thing is to select all the names in the Team Score sheet and name the "Names"

Very aware I'm probably being very stupid here, so apologies - but not sure what you mean by this or what to do?

 

[stuartk812]

Another option, that doesn't use volatile functions

=SUMPRODUCT(sumifs('Individual Scores'!$C$8:$C$15,'Individual Scores'!$A$8:$A$15,$B5:$C5))

I tried this, but it always returns a "0" - again am I doing something wrong? Apologies!