Want to find an alternative formula so calculations don't take so long. INDIRECT Function

Attached is a sample spreadsheet. Basically I want to try and find another way to gather the data in E6:G55 without using the INDIRECT function. The main spreadsheet I use has over 36,000 indirect function calls and its just taking way to long.

The way the spreadsheet is arranged, E3:G3(these can't be static as they change daily) determine how far back to gather the data based on date, then its averaged in E6:G55 via finding the row on each team sheet then using indirect.

The team sheets are constantly updated and the newest date is always on top.

Thanks.

Choose a formula based on sales team name:

Please Login or Register  to view this content.

---

Named Ranges:
ProdNames =Status!$T$1:$T$3
Team1 =Team1!$E$4:$G$24
Team1Dates =Team1!$D$4:$D$24
Team2 =Team2!$E$4:$G$24
Team2Dates =Team2!$D$4:$D$24
Team3 =Team3!$E$4:$G$24
Team3Dates =Team3!$D$4:$D$24
Team4 =Team4!$E$4:$G$24
Team4Dates =Team4!$D$4:$D$24
Team5 =Team5!$E$4:$G$24
Team5Dates =Team5!$D$4:$D$24
TeamNames =Status!$S$1:$S$5

Helper column J is used.

In J6, then drag down.

=MATCH(C6,INDIRECT("'"&D$6&"'!D1:D24"),0)+1

In E6, then drag Across.

=IFERROR(IF(E$3/1,IF(INT(E$3)=E$3,AVERAGE(OFFSET(INDIRECT($D6&"!$E"&$J6),0,COLUMNS($E6:E6)-1,E$3,1)))),"N/A")

Helper column Will drastically reduces time. Helper column can be hidden.