Update defined name ranges after sort

[JFU]

My application requires adding records (rows) on an ongoing basis, then
sorting on column A (Category). How do you get named ranges to update to
their new location after sorting? If I manually move a row, the referencing
hyperlink follows. However a data sort does not update the name pointer and
I have to go in and edit the range.

 

[Jacob Skaria]

Insert>Name>Define> give the name and use OFFSET() formula as below in refers
to

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))

If this post helps click Yes

 

[Don Guillett]

You can make them dynamic so they are self adjusting using OFFSET or match
or ....
=offset($a$1,0,0,counta($a:$a),5)

 

[JFU]

Thanks for the help

You can make them dynamic so they are self adjusting using OFFSET or match
or ....
=offset($a$1,0,0,counta($a:$a),5)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.