Deleting rows in a columns of numbers with increment '1' shows "#REF" How to pick up the above cell

[PruJakkam]

I have 15 cells with numbers 1 to 15 in a column and the formula in each cell is "=previou cell+1". If I delete a row in between, it shows "#REF" on all the bottom cells. How to fix this? I always want the cell to pick up the previous cell above even if I delete rows.

See the below example, I deleted the row with the number 11, and all the cells below turned into showing "$REF". Any thoughts?

[#2]

@PruJakkam 

in B4 above:

=XLOOKUP( ROW()-ROW(B$3), B$3:B3, B$3:B3) +1

[#3]

@PruJakkam 

The #REF error appears when deleting rows because your formula directly references the cell above it using its cell address (e.g., "=A2+1"). When you delete a row, the cell reference becomes invalid.

Here's how to fix this and ensure the formula picks up the previous cell even after deleting rows:

1. Use Relative References:

Instead of directly referencing the cell above, use a relative reference. This tells Excel to adjust the reference based on its own position. For example, instead of "=A2+1," use "=A1+1". This formula will always refer to the cell one row above, regardless of where it's copied or if rows are deleted.

2. Use OFFSET Function:

The OFFSET function allows you to dynamically reference a cell based on its position relative to another cell. Here's an example:

Excel

=OFFSET(A1,-1,0)+1

This formula:

• Starts with cell A1.
• Moves up 1 row using the -1 argument.
• Adds 1 to the value of the referenced cell.

This approach ensures the formula always refers to the cell above, even after deleting rows.

3. Use INDEX and MATCH Functions:

This method combines INDEX and MATCH to find the last non-empty cell above the current cell and add 1 to its value. Here's an example:

Excel

=INDEX(A:A,MATCH(TRUE,A:A<>"",MATCH(A1,A:A,0)-1))+1

This formula:

• Searches for the last non-empty cell in column A (A:A) using MATCH and TRUE.
• Adjusts the row number by subtracting 1 from the match position.
• Uses INDEX to retrieve the value from the adjusted row.
• Adds 1 to the retrieved value.

Applying the Solutions:

Replace your current formula in each cell with one of the solutions mentioned above. Copy the formula down to all cells in the column. Now, when you delete a row, the formula will automatically adjust to reference the correct cell above, avoiding the #REF error.

Remember to choose the solution that best suits your needs and understanding of Excel functions.

[#4]

@PruJakkam 

Yes, it would, wouldn't it?  I have been bitching for a decade about the appalling practices of traditional spreadsheets, such as the idea of relative references (to scan lists and arrays) and the garbage idea of referencing data by it location (A1 notation) rather than its content (named).  I couldn't think of a more error-prone strategy.  The surprise for me was that auditing shows only 90% of workbooks to be in significant error; I might have expected a higher figure. 

OK, that's the rant over!  It wasn't aimed at you; I just don't like the normal spreadsheet practice with its hard-to-memorise 'tips and tricks'.  Before 2018, I was stuck with what I was given and one way of dealing with the problem you outline is to use a defined name to reference the cell above

above "(defined with cell A2 active)"
= Sheet1!A1

or rather less ambiguously with the R1C1 notation

above
= Sheet1!R[-1]C

 Since there is only a single definition of the name, it cannot track individual cells, so you get what it says on the tin!  Of course, now I would simply use

= SEQUENCE(20)

and the problem no longer exists!

[#5]

@PruJakkam 

Take your pick