2

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?

  • INDIRECT is the needed function. – wbeard52 Nov 28 '15 at 18:39
3

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

=IF($A$1)

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.

=INDIRECT($A$2)     

enter image description here

More information and quote and image source

| improve this answer | |
1

I ran into the same problem earlier. This is how I referenced the cell above the current one (A2 in this case):

=OFFSET(A2,-1,0)

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

| improve this answer | |
  • The INDIRECT function is a better solution. – wbeard52 Nov 28 '15 at 18:40
  • Could you please shed some light on how 'INDIRECT' function can be used in this case? – Himanshu Mehani Dec 10 '15 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.