Make an entire row of data follow the lead cell in column A

The short answer is you cannot make a person's data "follow" [by using formulae] when the relationship is simply based on a row number . You will need VBA to do what you want i.e adding/deleting (and sorting) rows and ensuring that the data remains with its correct "owner".

An example: I add (insert) a new person at row 14 in Roster which immediately compromises the "Rota" sheet. In the Rota sheet I need to insert a new row (14) and then amend all entries in MH4 from 14 onwards to reflect the name/row relation. If people have been deleted, it is even more complex.

Hence each row has to be treated as an "entity" so whole rows are moved: hence the need for VBA.