INDEX across multiple sheets

[plotting]

Thank you so much for FlameRetired for getting me started on this but now I ran into another issue. In G4:13 I want it to be able to find the most points scored during the playoffs. This data is in 'Season 1'!AF2:AG13, Season 2'!AG2:AH13 and Season 3'!AG2:AH13. For now I put

Please Login or Register  to view this content.

You can see in G10,11 and 13 it gives an error because those scores happened in Season 2. For now I can manually change the above formulas from Season 1 to 2 but that will be tedious when there are 5+ seasons. I've tried nested IF statements that I couldn't get to work. The solution for the playoff highest scores would probably be the solution for the all time as well. Thanks for looking!

[plotting]

I figured out a bit of a workaround with this:

Please Login or Register  to view this content.

More than 2 seasons though might be a bit complicated though. Can anyone else see something better? Also I'm not sure that would return names for season 2 if the score occurred in season 1 but only once or twice. Example being 152 was scored twice in season 1 and season 2 but rather keep returning the same first occurrence

[XOR LX]

This data is in 'Season 1'!AF2:AG13, Season 2'!AG2:AH13 and Season 3'!AG2:AH13.

Hi.

It seems a touch inconvenient that the range of data in Season 1 is not consistent with those in the other two tabs. Is this unavoidable? If you increase the number of seasons, where will their data be placed?

Perhaps you could also re-post your workbook with your manually-calculated expected results entered, just so it's clear what is being aimed for.

Regards

[plotting]

If I had to insert a row and then hide it to make the range the same I could but I'm 99.9% sure it's not causing the issue because I can use AF2:AH13 in Season 1 calculations and it doesn't cause an issue, it's just a matter of easily combining them is where I can't get it to work now.

On the records tab in G4 I have:

Please Login or Register  to view this content.

but once I have to calculate for Season 3 and beyond it's going to be a really convoluted formula.

[XOR LX]

Hi.

If you first go to Name Manager and define:

Name: Seasons
Refers to: ={"Season 1","Season 2","Season 3"}

(Or whatever happen to be the sheet names in question.)

The formula in G4 of the Records tab is then:

=INDIRECT("'Season "&SUBSTITUTE(AGGREGATE(14,,COUNTIF(OFFSET(INDIRECT("'"&Seasons&"'!AG1:AH1"),{1;2;3;4;5;6;7;8;9;10},),H4)*{1,2,3}*10^5+{1;2;3;4;5;6;7;8;9;10},COUNTIF(H$4:H4,H4))+1,"000","'!A"))

Copy down as required.

Note that I decided to use static array constants here so that the construction is more efficient. The first of these - {1;2;3;4;5;6;7;8;9;10} represents the fact that there are 10 Teams to consider, and the second - {1,2,3} - that there are 3 sheets to consider.

Obviously this is fine if these numbers are fixed and/or quite small (it would not be difficult to amend the latter to account for more sheets), though - and particularly if the number of Teams is in reality much larger than 10, in which case extending the array constant I gave you may be a bit of an effort - if you prefer I can provide you with a more dynamic set-up, at the cost of a touch of efficiency and a longer formula.

Regards

[plotting]

That worked for Season 2 but not Season 1. If it just worked out to being me changing {1,2,3} to {1,2,3,4} and so on each season and then adding Season 4 to the name manger I wouldn't have any problem doing that. Thanks for looking at this for me.

[XOR LX]

You didn't make the column amendment in Season 1 so that the sheets are of an identical layout.

Regards

[plotting]

Ah dude you're a genius! Thank you so much! All I'll need to do each year is go to Name Manager then and add Season 4 and add to {1,2,3}?

[XOR LX]

Ah dude you're a genius! Thank you so much! All I'll need to do each year is go to Name Manager then and add Season 4 and add to {1,2,3}?

You're welcome! And yes, that is correct. Amend the other array if the number of Teams increases also.

I should point out that the solution I offered you is not the most flexible, and indeed only works due to certain particulars in your set-up. For example, I took advantage of the fact that you have named your sheets "Season 1", "Season 2", etc., by locating the relative position of the sheet in which each particular value occurs. This would not have been possible if you had not named your sheets in this fashion (i.e. a constant name followed by increasing integers).

And the "'!A" at the end indicates that the names are to come from column A, in case you didn't realise.

If you decide to change your layout in future and run into difficulties, reply in this thread and I should pick up on it.

Regards

[plotting]

Yeah I did notice it was substituting after the space in season and getting the column for A. I will keep the same format going forward so this shouldn't be an issue and making 2 minor changes once a year isn't a big deal. Thanks again!

[plotting]

Hey XOR LX I made a new sheet (Top-Bottom 50), wanting to get the top and bottom 50 scores ever and I ran into a few reference errors with the formula. Seems it's either trying to pull Season 4 or doesn't return a season and replaces it with 11, 11 is the correct column so I was hoping you could see where the error was when it's evaluating cause I can't tell what happens in certain steps. Thanks in advance!

Fantasy football.xlsx

[plotting]

I think I figured out why it happens, seems to be the times someone got that score in the same season if that makes sense? 67 was scored twice by Nate in Season 1 for example

[XOR LX]

Ah, yes. I didn't account for that possibility (or perhaps I did but thought that you'd still only want that record returning a single time).

Apologies. Bear with me whilst I think of an alternative.

Regards

[XOR LX]

Ok, I can help you with this, but it's necessarily more complex. More importantly, I'd need you to reattach your workbook after making a few changes.

The formatting/layout set-up in there is quite inconsistent, which is hindering solutions. For example, for some reason you've decided to merge cells for Weeks 1-14, though not for Weeks 15 and 16? Merged cells are in general a terrible idea, as most members of this forum will tell you. However, at least if you are consistent then we can work around this.

I suggest that you make the data in Weeks 15 and 16 consistent with the rest, in that they each cover two merged cells. Ditto for the the Playoffs breaker in between.

If you can make those changes and re-upload then I should be able to get you a solution.

Regards

[plotting]

I fixed those columns, thank you so much for looking at this!

[XOR LX]

Ok, so your formula in cell C3 of the Top-Bottom 50 tab now needs to become:

=LARGE('Season 1:Season 3'!$B$2:$AH$11,ROWS($1:1))

to account for these extra columns.

You'll also need to transpose the definition for Seasons which I gave you previously, i.e. make it:

={"Season 1";"Season 2";"Season 3"}

The formula in cell B3 of the Top-Bottom 50 sheet is then:

=INDEX(INDIRECT("'Season "&MATCH(TRUE,MMULT({1,0,0;1,1,0;1,1,1},MMULT(COUNTIF(OFFSET(INDIRECT("'"&Seasons&"'!B1:AH1"),{1,2,3,4,5,6,7,8,9,10},),C3),{1;1;1;1;1;1;1;1;1;1}))>=COUNTIF(C$3:C3,C3),0)&"'!A2:A11"),SMALL(IF(N(OFFSET(INDIRECT("'Season "&MATCH(TRUE,MMULT({1,0,0;1,1,0;1,1,1},MMULT(COUNTIF(OFFSET(INDIRECT("'"&Seasons&"'!B1:AH1"),{1,2,3,4,5,6,7,8,9,10},),C3),{1;1;1;1;1;1;1;1;1;1}))>=COUNTIF(C$3:C3,C3),0)&"'!B1"),{1;2;3;4;5;6;7;8;9;10},{0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32}))=C3,{1;2;3;4;5;6;7;8;9;10}),-LOOKUP(1,CHOOSE({1,2},-COUNTIF(C$3:C3,C3),-(1+COUNTIF(C$3:C3,C3)-LOOKUP(COUNTIF(C$3:C3,C3),1+MMULT({1,0,0;1,1,0;1,1,1},MMULT(COUNTIF(OFFSET(INDIRECT("'"&Seasons&"'!B1:AH1"),{1,2,3,4,5,6,7,8,9,10},),C3),{1;1;1;1;1;1;1;1;1;1}))))))))

Copy down as required.

Apologies for the complexity, though I cannot see a more succinct solution. Also, be aware that I have taken the liberty to once again use static constants here. This will be fine if your ranges stay as they are, though bear in mind that, if the number of sheets/rows/columns is to be changed then these constants will also require amending.

Of course, I can replace them with more dynamic constructions at the cost of some performance if you like.

Regards

[plotting]

Dude you are a genius, worked like a charm, tried to give you more rep but it won't let me because you were the last one I gave it to. Obviously adding ;"Season 4" to the name manger will need to be done but I don't see a 1,2,3 this time so what other changes? MMULT({1,0,0;1,1,0;1,1,1} to MMULT({1,0,0,0;1,1,0,0;1,1,1,0;1,1,1,1} in those 3 spots? also I noticed you did 0-32 to reference the columns but AH is 34th? I tested it and I worked just wondering how this constant is being used, if it's complicated you don't need to explain, thanks again man!

[XOR LX]

Dude you are a genius, worked like a charm, tried to give you more rep but it won't let me because you were the last one I gave it to.

I know. Don't worry about it! Thanks anyway.

MMULT({1,0,0;1,1,0;1,1,1} to MMULT({1,0,0,0;1,1,0,0;1,1,1,0;1,1,1,1} in those 3 spots?

Wow! I'm impressed at your knowledge of matrix multiplication. That's correct.

I noticed you did 0-32 to reference the columns but AH is 34th?

True, but I'm offsetting from cell B1, and column AH is 32 columns on from that reference.

Note also the need for even offsets only, thanks to your (annoying) habit of merging cells.

Cheers

[plotting]

Haha, awesome again I can't thank you enough. I generally try to avoid merged cells but I didn't want to make another sheet for the data below the scores or have it run off the page because it has so much more data, won't make that mistake again though!