Less intensive alternative to FILTER function?

[AYouQueTai]

Hello,

I have the following formula in my NHL spreadsheet.
=FILTER(INDIRECT("'"&[@Team]&"'!$E$2:$E$300"),(INDIRECT("'"&[@Team]&"'!$B$2:$B$300")<=[@Date])*(INDIRECT("'"&[@Team]&"'!$C$2:$C$300")>=[@Date])*(INDIRECT("'"&[@Team]&"'!$A$2:$A$300")=[@Jersey]))
The problem is it is taking too much time to update the sheet. I have switched to manual calculation but 2 weeks into the season (2000 rows of data) its taking 5 minutes to update.
Is there another formula that would accomplish the same thing while being less intensive?

Each team has a worksheet where every player who has played a game for them this season is listed along with their stats.
I also have a roster sheet where I input the gameday roster of 20 players who will be on the bench that game.
when I input their jersey number in the roster sheet it looks up the team worksheet, checks the start date and end date of that players status to make sure they weren't traded or injured etc,,, and lists their name

Any input on which function I should try instead?

 

[Domenic]

Instead of using the INDIRECT function to point to the target sheet and range, use the CHOOSE function. For example, for simplicity, let's say that you have 2 sheets named Team A and Team B, you would replace...

INDIRECT("'"&[@Team]&"'!$E$2:$E$300")

with

CHOOSE(MATCH([@Team],{"Team A","Team B"},0),'Team A'!$E$2:$E$300,'Team B'!$E$2:$E$300)

And you would do the same for your other arguments in the formula. Actually, after doing a quick search on Google, it looks like there are 32 teams in the NHL. In this case, I would suggest that you list the teams in a range of cells, let's say G2:G33. Then you could do the following instead...

CHOOSE(MATCH([@Team],$G$2:$G$33,0),'Team A'!$E$2:$E$300,'Team B'!$E$2:$E$300 . . . )

Hope this helps!

 

[AYouQueTai]

Sorry about the delay. Thank you!

 

[Domenic]

You're very welcome, cheers!