Offset formula breaks if I delete a row

Norbertinho

New Member
Joined
Feb 1, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.

Excel Formula:
=OFFSET(INDEX(A:A,2),0,0,COUNTA(OFFSET(INDEX(A:A,2),0,0,5000,1)),1)
 
Upvote 0
Solution
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.

Excel Formula:
=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!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top