Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JFU JFU is offline
external usenet poster
 
Posts: 2
Default Update defined name ranges after sort

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default Update defined name ranges after sort

InsertNameDefine 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
---------------
Jacob Skaria


"JFU" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Update defined name ranges after sort

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

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


  #4   Report Post  
Posted to microsoft.public.excel.misc
JFU JFU is offline
external usenet poster
 
Posts: 2
Default Update defined name ranges after sort

Thanks for the help

"Don Guillett" wrote:

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

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


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
summing to defined ranges keerthyV Excel Worksheet Functions 2 April 8th 09 08:05 AM
Create new list with defined ranges BKO Excel Worksheet Functions 0 June 13th 07 11:35 AM
Create new list with defined ranges BKO Excel Worksheet Functions 0 June 13th 07 11:30 AM
dynamic defined ranges Thomas Pike Excel Worksheet Functions 1 September 14th 05 12:29 AM
How do I group data using defined ranges? Yenabi Excel Worksheet Functions 2 May 16th 05 06:33 PM


All times are GMT +1. The time now is 12:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"