Deleting rows of data causing #REF error

[Stuepef]

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:
Bobby Jones
Richard Haden
Scott White
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)),"")

Jones
Haden
White
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:
Jones
White
Anderson

 

[StephenCrump]

Here's one way you could do it:

MyNames: A2:A5
D8: =IFERROR(INDEX(MyNames,ROW()-ROW(D$8)+1),"")

You could build your formula into this, i.e. replacing MyNames with the formula. But I'd be inclined to have all the new row formulae referencing that row, rather than the original row, e.g.

=IFERROR(RIGHT(D8,LEN(D8)-FIND(" ",D8)),"")

Book1
	A	B	C	D
1	MyData
2	Bobby Jones
3	Richard Haden
4	Scott White
5	Vanessa Anderson
6
7				Copy
8				Bobby Jones
9				Richard Haden
10				Scott White
11				Vanessa Anderson
Sheet1

 

[MartyS]

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 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] 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:
Bobby Jones
Richard Haden
Scott White
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)),"")

Jones
Haden
White
Anderson

My question is if a user deletes Richard Haden for example, cell H101 produces a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] 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:
Jones
White
Anderson

The INDIRECT function will stop the #REF problem, but you can't copy it down the page (you'd have to manually edit each cell reference)

Fortunately the OFFSET function seems to work just fine!


Does this work for you?


=IFERROR(RIGHT(OFFSET(A1,0,0),LEN(OFFSET(A1,0,0))-FIND(" ",OFFSET(A1,0,0),1)),"")


Paste into the cell you want to start your name-trimming... and copy down! (Would that be cell H101?!)

 

[Rick Rothstein]

A few users are deleting rows that are causing
What about protecting the worksheet setting it up so they cannot delete (or insert) any rows.

 

[Stuepef]

@MartyS - If I use the formula you supplied and I delete Richard Haden, my result is the following:
Jones
(BLANK)
White
Anderson

I am hoping to get the solution to look like this:
Jones
White
Anderson

 

[MartyS]

NOTE1:

There is a cell reference (currently set to [$H$5]) that needs to be set correctly. (See below for details)
There are 3 (three) instances of it in the formula. ALL three need to be changed.


NOTE2:

The formula expects the cell range containing the full name to be in the range of [A1:A1000]
If your FullName source data is in a different column, starts at a different row, or goes over 1000 rows, you'll need to amend the 3 (three) references in the formula to the correct range.
Probably best to set this range to well over the highest number of records. i.e. if you have 1000 names, set it to 2000 or 5000 to be on the safe side, assuming it doesn't interfere with other data in your sheet.




Formula


=IFERROR(RIGHT(INDEX(OFFSET(INDIRECT("A1:A1000"),0,0),ROW()-ROW(OFFSET($H$5,0-1,0))),LEN(INDEX(OFFSET(INDIRECT("A1:A1000"),0,0),ROW()-ROW(OFFSET($H$5,0-1,0))))-FIND(" ",INDEX(OFFSET(INDIRECT("A1:A1000"),0,0),ROW()-ROW(OFFSET($H$5,0-1,0))),1)),"")




Let's say you paste this into cell [H101]

Before you copy it down, make sure you've changed the three references from $H$5 to $H$101

It must be absolute (dollar signs on both the Column Letter and the Row Number)

Once that adjustment has been made in all three places, you can copy it down as far as you need (further than you need so if more names are added, they automatically appear)



With this formula, you should be able to delete rows and add rows above the cell you pasted the formula in and it'll auto-update.


Hope that helps!

 

[Stuepef]

Thank you, @MartyS! I don't understand what is going on in the formula, but it works like a charm! Now to update the rest of my formulas...

 

[MartyS]

Glad it works