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?
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
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
I ran into the same problem earlier. This is how I referenced the cell above the current one (A2 in this case):
However, when adding rows, formula needs to be copied and pasted. Unless, a script is created to do that automatically.
For more information on offset formula: https://support.office.com/en-us/article/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66