cell names sorting with data (2003/2007)

Topic

New Reply

We have a spreadsheet with lots of named cells – we tried sorting a selected range by date and found that the cell names did not move with the data. If we dragged to a new position they did. Is there any way to make this work?

Thanks Robeta

Reply | Quote

Replies

This is a known property of sorting: see XL: Defined Names Not Sorted in Excel.

When you sort a range, you only sort the cell values, not the cell references. For the same reason, formulas referring to individual cells within the sorted range will not be updated either.

Reply | Quote

Some options:
If the formulas being sorted are the issue, make the references absolute. and they will still refer to the same cells

If the issue is that the items being sorted are referenced by other cells elsewhere in the workbook, you can, instead of refering to particular cells (whose contents will change on sorting) use some kind of lookup (vlookup, index/match or even an offset) to refer to the item of interest. Essentially make the formulas work regardless of the sorting.

Create your own sorting routine which moves cells instead of copying copying.

Steve

Reply | Quote

Thanks for your help – I am going to have to look at some sort of data referencing for them. Nearly scuppered the upgrade to 2007 this am as originally the dept thought this was a bug!!!

Reply | Quote