Help with using Offset for Row #REF error

[Barbz]

Hi Everyone and thank you in advance for your help.

I have this complex (for me) formula on a large spreadsheet. As part of the process I must create copies of the spreadsheet with only the rows that meet "manager" as criteria.
This means I'm often deleting the first row of data which then results in a #Ref error in formula in below in the area I've made bold.

=IFERROR(IF(J142="","",INDEX($A$2:$A$126,AGGREGATE(15,6,(ROW($N$2:$N$126)-ROW($N$2)+1)/($N$2:$N$126=L142),COUNTIF($L$142:L142,L142))))," ")

I know I can use OFFSET but I'm not sure how or where to apply it in this particular formula. Or if it will work in this formula.

Any assistance is very appreciated.

 

[pgc01]

Hi
Welcome to the board

Instead of

... ,(ROW($N$2:$N$126)-ROW($N$2)+1)/ ...

try

... ,(ROW($N$2:$N$126)-MIN(ROW($N$2:$N$126))+1)/ ...

 

[Barbz]

Thank you so much PGC. The change works perfectly. And now I have something new to learn about; the MIN function. I've been researching for days and never came across this as a possible solution. So I can't tell you how much your quick response means to me. Thanks again!

 

[pgc01]

You're welcome. Thanks for the feedback.