Hi TimR,
One way is to copy all the data then paste it somewhere else on the
sheet, but not an ordinary paste, do Paste SpecialPaste All then click
the Paste Links button.
Then with the pasted column of Names select the top name and type in
the following formula (I have assumed that the original top name is in
A2)...

=IF(A2="","Current Resident",A2)

Fill this formula down to the bottom of the pasted data to replace the
pasted link formula and you should get the desired result.

Hope this helps

Ken Johnson