Losing Absolute Cell Reference

[kod2th3e]

I have the following formula in a cell on one work sheet:

{=IF(ROWS($B$4:$B$4)>COUNTIF('Lab Submissions Ad 1'!$N:$N,"upper"),"",INDEX('Lab Submissions Ad 1'!$A:$A,SMALL(IF('Lab Submissions Ad 1'!$N:$N="upper",ROW('Lab Submissions Ad 1'!$N:$N)-ROW('Lab Submissions Ad 1'!$N$2)+2),ROWS($B$4:$B$4))))}

When I delete a row of data on the sheet that this formula looks at, the formula errors out because it loses it's reference to $N$2 even though I am utilizing the "$" to make it an absolute reference.

Any idea on a work around or how to solve this issue?

 

[RoryA]

If you delete row 2 on that sheet, you will always get a REF error from a formula referring directly to a cell in that row. You could simply use 2 rather than ROW('Lab Submissions Ad 1'!$N$2) if you really don't want it to adjust.

 

[kod2th3e]

RoryA,
Thanks for the advice, this worked like a charm.

If you delete row 2 on that sheet, you will always get a REF error from a formula referring directly to a cell in that row. You could simply use 2 rather than ROW('Lab Submissions Ad 1'!$N$2) if you really don't want it to adjust.