Excel #REF error with OFFSET formmula

Question

I have an OFFSET formula referencing another sheet. The active row is deleted and the remaining rows shift up when i click button 1. everything works but i keep getting a #REF error when the rows shift up. Is there a better formula to use to avoid such error?

Here is what I have:

Active Cell is C7, clicking button 1 will clear that row on a a different sheet and shift the bottom rows up

Here's what happens after

Answer 1

Try,

=index(sheet2!c:c, 2+$a$34)