Offset formula breaks if I delete a row

[Norbertinho]

Hi all.

I have an a list formula defined as such in my excel template:

=OFFSET($A$2,0,0,COUNTA(OFFSET($A$2,0,0,5000,1)),1)

If I delete one(or more) rows from anywhere beside the "first" ,actually the 2nd row where my offset points, it works ok. But if you delete the A2 row, the formula breaks as follows:

=OFFSET(#REF!,0,0,COUNTA(OFFSET(#REF!,0,0,5000,1)),1)

Any ideas why this is happening? Sorry if it was already asked, I could not find other posts with similar content where offset was used.

Thanks,Norbert

 

[Peter_SSs]

Welcome to the MrExcel board!

Any ideas why this is happening?

Yes, because A2, which your formula is using, no longer exists if you delete row 2.

I'm not exactly sure what you are trying to do, but see if this overcomes the problem.

=OFFSET(INDEX(A:A,2),0,0,COUNTA(OFFSET(INDEX(A:A,2),0,0,5000,1)),1)

 

[Norbertinho]

Welcome to the MrExcel board!

Yes, because A2, which your formula is using, no longer exists if you delete row 2.

I'm not exactly sure what you are trying to do, but see if this overcomes the problem.

=OFFSET(INDEX(A:A,2),0,0,COUNTA(OFFSET(INDEX(A:A,2),0,0,5000,1)),1)

Thank you so much. Worked like a charm!

 

[kvsrinivasamurthy]

This also works

=OFFSET(INDIRECT("A"&2),0,0,COUNTA(OFFSET(INDIRECT("A"&2),0,0,5000,1)),1)

 

[Norbertinho]

This also works

=OFFSET(INDIRECT("A" &2),0,0,COUNTA(OFFSET(INDIRECT("A" &2),0,0,5000,1)),1)

Thank you

 

[Peter_SSs]

Thank you so much. Worked like a charm!

You're welcome. Thanks for the follow-up.