I am making an excel spreadsheet that will breakdown and analyze the football season. I am currently using the results from each round to retrieve who vs'd who to make it easier to identify which teams a team has faced. Each round sheet holds the result for that particular round. What I would like to do is have the Round 1 sheet identifier update to round 2, round 3, etc when I use autofill rightward.
I understand Indirect is supposed to be useful for this but I cannot make it work no matter how I try it. Even in isolation I have not been able to make it work, so I might be retarded.
using this formula
=IFNA(VLOOKUP('Scoreless Fixture'!$A2,'Round 1'!$A$4:$D$12,3,FALSE),INDEX('Round 1'!$A$4:$A$12,MATCH($A2,'Round 1'!$C$4:$C$12,0)))
to retrieve the teams. The teams are presented as Home vs Away so that is why I have chosen to use this formula. It works for it's purpose but for automation I would like to have the round number update.
When I introduce indirect I fail miserably. If someone could give me some pointers would be greatly appreciated.
This is untested but try this formula ...
=IFNA(VLOOKUP('Scoreless Fixture'!$A2,INDIRECT("'Round " & SelectedRound & "'!$A$4:$D$12"),3,FALSE),INDEX(INDIRECT("'Round " & SelectedRound & "'!$A$4:$A$12"),MATCH($A2,INDIRECT("'Round " & SelectedRound & "'!$C$4:$C$12"),0)))
... to make it work, you need to create a named range called SelectedRound and put the integer value in that represents the round, e.g. 1 or 2 or 3 or 4, etc.
I don't have all of your data but if I simplified that and did the "INDIRECT" portion only on a single cell, it worked fine.
Here's hoping it does what you want.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments