Stop ranges within formula from moving when a new row is added

[ThomG]

My array formula below works BUT, I think I need to apply maybe an INDIRECT function to stop the ranges from moving when I add a new row to my data set. Can anyone help?

{=IFERROR(INDEX('EIRF Tracker '!$N$9:$N$2000,MODE(IF('EIRF Tracker '!$G$9:$G$2000="Injury",IF('EIRF Tracker '!$B$9:$B$2000="2018-19",IF('EIRF Tracker '!$C$9:$C$2000=C54,IF('EIRF Tracker '!$N$9:$N$2000<>"0",MATCH('EIRF Tracker '!$N$9:$N$2000,'EIRF Tracker '!$N$9:$N$2000,{0,0}))))))),"None")}

Thank you in advance!
Gem

 

[Eric W]

My array formula below works BUT, I think I need to apply maybe an INDIRECT function to stop the ranges from moving when I add a new row to my data set. Can anyone help?

{=IFERROR(INDEX('EIRF Tracker '!$N$9:$N$2000,MODE(IF('EIRF Tracker '!$G$9:$G$2000="Injury",IF('EIRF Tracker '!$B$9:$B$2000="2018-19",IF('EIRF Tracker '!$C$9:$C$2000=C54,IF('EIRF Tracker '!$N$9:$N$2000<>"0",MATCH('EIRF Tracker '!$N$9:$N$2000,'EIRF Tracker '!$N$9:$N$2000,{0,0}))))))),"None")}

Thank you in advance!
Gem

Everywhere you have a range, just substitute an INDIRECT. For example, the part in red becomes:

INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2000"),

 

[ThomG]

Thanks Eric! I have just attempted this and Excel is telling me I have too few arguments... can you spot anything obvious?

=IFERROR(INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2000"),MODE(IF(INDIRECT("'EIRF Tracker '!$G$9:$G$2000"="Injury",IF(INDIRECT("'EIRF Tracker '!$B$9:$B$2000"="2018-19",IF(indirect("'EIRF Tracker '!$C$9:$C$2000"=C55,IF(indirect("'EIRF Tracker '!$N$9:$N$2000"<>"0",MATCH(indirect("'EIRF Tracker '!$N$9:$N$2000",(indirect("'EIRF Tracker '!$N$9:$N$2000",{0,0}))))))),"None")

Gem

 

[Eric W]

Try:

=IFERROR(INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2000"),MODE(IF(INDIRECT("'EIRF Tracker '!$G$9:$G$2000")="Injury",IF(INDIRECT("'EIRF Tracker '!$B$9:$B$2000")="2018-19",IF(INDIRECT("'EIRF Tracker '!$C$9:$C$2000")=C54,IF(INDIRECT("'EIRF Tracker '!$N$9:$N$2000")<>"0",MATCH(INDIRECT("'EIRF Tracker '!$N$9:$N$2000"),INDIRECT("'EIRF Tracker '!$N$9:$N$2000"),{0,0}))))))),"None")

 

[ThomG]

Success! Thank you kindly

 

[Eric W]

Glad to help.