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

ThomG

New Member
Joined
Feb 18, 2019
Messages
4
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,231
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

New Member
Joined
Feb 18, 2019
Messages
4
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,231
Try:

Code:
=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")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,320
Messages
5,527,996
Members
409,798
Latest member
Snake68

This Week's Hot Topics

Top