I have column B with a formula full of IFs with each cell referenced to the cell above it.
From time to time, I need to delete some rows. Once a row is deleted, the formula in column B gives #REF for the remaining cells beneath the deleted row.
How can I delete a row and the formula is automatically updated by referencing the new cell above it?
Also, when adding a row, can the formula automatically take place in cell B without me adding it each time?
Excel – How to add/remove a row without affecting formula in a column in Excel
microsoft excel
Best Answer
Depends on your situation and layout really. Depending on that, there are 2 options.
Commented out You could use absolute references which I think is what you want, such as
The $ means it sticks to that cell, what ever the value!
However, depending on what your deleting, you may find the use of Indirect useful
More information and quote and image source