Forum Discussion
Excel formula help
This is the difficult part!
Anyway, I've updated the workbook with another formula.
Hi Again,
So I tried to add another team and wasn't able to do it. Could you help with this and then maybe I'll understand it better? Thanks!
Dean
Hi,
In this case, all you have to do is expanding the ranges in the second part of the formula as follows:
=IFERROR(
INDEX('4th-5th Draft'!$B$1:$N$1,IFERROR(MATCH(C37,'4th-5th Draft'!$B$4:$N$4,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$5:$N$5,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$6:$N$6,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$7:$N$7,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$8:$N$8,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$9:$N$9,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$10:$N$10,0),
MATCH(C37,'4th-5th Draft'!$B$11:$N$11,0))))))))),
INDEX('4th-5th Draft'!$B$14:$R$14,IFERROR(MATCH(C37,'4th-5th Draft'!$B$17:$R$17,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$18:$R$18,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$19:$R$19,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$20:$R$20,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$21:$R$21,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$22:$R$22,0),
IFERROR(MATCH(C37,'4th-5th Draft'!$B$23:$R$23,0),
MATCH(C37,'4th-5th Draft'!$B$24:$R$24,0))))))))))
Please note that the Range is now from B to R instead of B to N as the previous one.
The reason why I updated only the second Index is that you've added the new team next to the second group of teams, not below it as a new group.
If you keep adding the new teams this way (to the right), I can make the formula dynamic for you as follows:
=IFERROR(
INDEX('4th-5th Draft'!$1:$1,IFERROR(MATCH(C2,'4th-5th Draft'!$4:$4,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$5:$5,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$6:$6,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$7:$7,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$8:$8,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$9:$9,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$10:$10,0),
MATCH(C2,'4th-5th Draft'!$11:$11,0))))))))),
INDEX('4th-5th Draft'!$14:$14,IFERROR(MATCH(C2,'4th-5th Draft'!$17:$17,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$18:$18,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$19:$19,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$20:$20,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$21:$21,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$22:$22,0),
IFERROR(MATCH(C2,'4th-5th Draft'!$23:$23,0),
MATCH(C2,'4th-5th Draft'!$24:$24,0))))))))))
This is done by targeting the entire rows instead of a specific range of them.
Now you can add as many teams as you want to the right of the first or the second group.
Hope that helps
Hi,
I'm sorry, but this is not easy!
You need to rearrange the data in Teams sheet to make the players to next coach head.
You can hide these extra columns or make their font color White.
After that, you can use some sort of functions to link them to the Game 1 sheet.
Please see the attached file.
Oh my goodness. That's incredible! Thanks so much!
Dean
I have another problem that I wanted to see if you could help with. If not, that's ok. Attached is another Excel sheet. I want to be able to track teams playing against teams without having to copy paste everything each time. If I plug in a coaches name in game 1, I want the players rostered. Then when I put in the points for each player, I want that to put the number into the Totals tab so that can track the players points during the season. Is this something that Excel can do? Thanks!
DeanHi again,
You can use this formula for each request column:
=IFERROR(INDEX(CONTACT!I:I,MATCH(B4,CONTACT!B:B,0))&"","")
But it will return the full player name that corresponds to each member in the Contact sheet.
Hi,
I have one more request. Under the tab 6th-8th draft, I wanted to be able to add player requests that are linked with the player name to the player they want. Is that possible? So for example for Lauren Carter's team, in round 3, player Carter has a request for player Maunu, but that information is on the contact page. Can that work? Thanks!
DeanGreat! That's just amazing what you can do with Excel. I need to learn more for sure!
Dean