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?

One possible approach, add a header row and make that the first row of the
range, protect the cells and the user cannot remove?

..

--
Cheers
Nigel



"Keith" wrote in message
...
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?

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.

By 'very first row' I assume you mean to A2, first row below the header in
A1

Your offset is relative to A2 so if you 'move' another cell to A2, the A2 in
the original formula no longer exists. To demonstrate try this -

=$A$2
now drag a cell to A2, I expect the formula will now return #REF!

Try amending your Offset -
=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A),2)

Regards,
Peter T

"Keith" wrote in message
...
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?

Forgot to reduce count & row height by 1 to exclude the header row

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

Peter T

"Peter T" <peter_t@discussions wrote in message
...
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.

By 'very first row' I assume you mean to A2, first row below the header in
A1

Your offset is relative to A2 so if you 'move' another cell to A2, the A2
in
the original formula no longer exists. To demonstrate try this -

=$A$2
now drag a cell to A2, I expect the formula will now return #REF!

Try amending your Offset -
=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A),2)

Regards,
Peter T

"Keith" wrote in message
...
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?

Peter,

Your solution worked perfectly.

Thanks very much. I was really stuck on this one.

Keith

"Peter T" wrote:

Forgot to reduce count & row height by 1 to exclude the header row

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

Peter T

"Peter T" <peter_t@discussions wrote in message
...
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.

By 'very first row' I assume you mean to A2, first row below the header in
A1

Your offset is relative to A2 so if you 'move' another cell to A2, the A2
in
the original formula no longer exists. To demonstrate try this -

=$A$2
now drag a cell to A2, I expect the formula will now return #REF!

Try amending your Offset -
=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A),2)

Regards,
Peter T

"Keith" wrote in message
...
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?