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

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:

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:

• 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.