I have two named ranges. The first is a list of hospitals and is in Column
A, it is used as a validation list for a column on another sheet.

The second is Column A again along with Column B (which contains the City
the hospital is in). It is used so that when the user selects the correct
hospital the City is automatically input using the vlookup function.

This works great.

I also have a button on the worksheet with the named ranges that sorts the
lists alphabetically.

The Idea is that the user can add a new hospital to the bottom of the list
then click the button to re-sort the list. Again this works fine.

The user can also remove an entry from the list and tell Excel to move the
cells up so there are no gaps in the list. This is where I start having
problems.

If the entry is removed from somewhere in the middle of the list for example
then everything is fine, but if the entry to be removed is the very first row
of the named range then I get a #Ref error in the ranges definition.

The definition I use for the A and B range is:

=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,2)

How can I get around this problem?