How do I use the Offset function?

Question

Level 1

40 points

Hello and thanks for reading this.

I have a simple question with needs a very simple answer but with my simple intelligence I'm still scratching my bold head. 😊

I created a column with the #1 on top and then, underneath the formulas "=B2+1"; "B3+1" and so on...

If I erase 1 row, the rows after that show an error ("The formula contains an invalid reference").

Looking for in the web I found that I can avoid it using the Offset function.

Could you please help me with the syntax? What exactly I need to write on B3 and on?

Very thankful for any help.

GM

MacBook Pro 15”, 10.15

Posted on Sep 12, 2020 8:36 AM

Reply

Answer 1

Top-ranking reply

Level 7

30,366 points

What result do you want? Do you want it to go 1,2,4,5.. or renumber the lower rows so it remains 1,2,3,4...?

If you want 1,2,3,4..., there are a few ways.

Formula in B3 = OFFSET(B3,-1,0)+1

fill down to complete column

You could start using the formula at cell B2 (instead of typing in a "1") if you leave B1 blank.

or

All rows in B, including row 2 =ROW()-1

If you want 1,2,4,5.. , get rid of the formulas and just use the numbers.

Reply

Answer 2

Level 1

40 points

Thank you Badunit!

It worked. Did exactly what I needed.

Thank you God for making people who are knowledgeable, kind and nice and willing to share their wisdom to help others!

Man! Did I find my muse?! 😊

There must be somewhere there a prayer for forum(s) and if there isn't yet, that should be it! 😊

I wanted to be able to erase one row and keep the count reflecting the change (5 turn into 4 and so on).

Appreciate your time!

Thanks again.

GM

Reply