CODEWITHSUNDEEP
excel-formulaexcel-2013excel-indirect
Hery0502
Hery0502

Reputation: 91

Deleting of rows causing #REF errors

I have a worksheet that users input employee names and date of birth for various calculations. A few users are deleting rows that are causing #REF errors with the calculations and I am looking for a solution around it. Indirect or Offset are options I am considering to solve the issue.

Here is my data in Column A:

  1. Bobby Jones
  2. Richard Haden
  3. Scott White
  4. Vanessa Anderson

Column H starting at cell 100 I have a calculation to trim the first name:

=IFERROR(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),"")
  1. Jones
  2. Haden
  3. White
  4. Anderson

My question is if a user deletes Richard Haden for example, cell H101 produces a #REF error. How do I add a Indirect or Offset function to the formulas in Column H to eliminate the possibility of REF errors when a row is deleted?

Results I am looking for with the above scenario:

  1. Jones
  2. White
  3. Anderson

Thank you for the help!

Upvotes: 0

Views: 4001

Answers (3)

Chronocidal
Chronocidal

Reputation: 7951

A problem you will see with this solution is that the trim-rows creep up as other rows are deleted. This is why I prefer to have calculations hidden on a seperate tab.

However, you can combine OFFSET with ROW to say "The row that is as far from POSITION1 as I am from POSITION2". If we make POSTION1 = $A$1 (which means it will fail if row 1 is deleted - best to have a header row!) and POSITION2 = $H$100, then you can say

OFFSET($A$1,ROW()-ROW($H$100),0)

Some examples

Cell H100: Row() - Row($H$100) = 100 - 100 = 0 | OFFSET($A$1, 0, 0) = A1

Cell H101: Row() - Row($H$100) = 101 - 100 = 1 | OFFSET($A$1, 1, 0) = A2

Cell H110: Row() - Row($H$100) = 110 - 100 = 10 | OFFSET($A$1, 10, 0) = A11

Now, let's delete row 2. This shifts H100 to H99, H101 to H100 and H110 to H109:

Cell H99: Row() - Row($H$99) = 99 - 99 = 0 | OFFSET($A$1, 0, 0) = A1

Cell H100: Row() - Row($H$99) = 100 - 99 = 1 | OFFSET($A$1, 1, 0) = A2

Cell H109: Row() - Row($H$99) = 109 - 99 = 10 | OFFSET($A$1, 10, 0) = A11

(The other problem I can see with your formula is people with two 2 forenames, such as racing driver "Juan Pablo Montoya Roldán" - his forename is "Juan Pablo", his surname is "Montoya Roldán")

Upvotes: 0

TomJohn
TomJohn

Reputation: 747

You may try something like this:

First step: Add header cell to you name list (cell A1) for example "Names"

Second step: Add counter in column F from F100 (counter i.e. 1, 2, 3, 4)

Use offset (from cell H100):

=IFERROR(RIGHT(OFFSET($A$1,F100;0),LEN(OFFSET($A$1,F100;0))-FIND(" ",OFFSET($A$1,F100;0),1)),"")

Quick reference how to use OFFSET: https://exceljet.net/excel-functions/excel-offset-function. Logic behind: as you have counter you will always get cell X rows below your header. Deleting rows will not change anything.

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

You could use INDEX to return the cell at the row number junction in the column.

For example, INDEX($A:$A,1) will always return the value in the first cell of column 1. INDEX($A:$A,2) will always return the second cell, etc.

So:
=IFERROR(MID(INDEX($A:$A,1),FIND(" ",INDEX($A:$A,1))+1,LEN(INDEX($A:$A,1))),"")
will show the new first row if the existing one is deleted.

Note:
I usually use =MID(A1,FIND(" ",A1)+1,LEN(A1)) rather than =RIGHT(A1,LEN(A1)-FIND(" ",A1)) but no idea which is faster.

Note 2: Use ROW()-99 rather than 1 in H100 to return the correct row number.
=IFERROR(MID(INDEX($A:$A,ROW()-99),FIND(" ",INDEX($A:$A,ROW()-99))+1,LEN(INDEX($A:$A,ROW()-99))),"")

Note 3: INDEX is non-volatile while OFFSET is (recalculates every time).

Upvotes: 1

Related Questions