keeping cell references after sorting

Hi,

I have a sheet with data in a range of cells, say for example A1:J6. In another part of the same sheet, I have cells that link to the ones in that range (A1:J:6).

If I insert cells into A1:J6 and add data (so now my range of cells with original data is A1:L6), the linked cells in the other part of the sheet automatically update with the correct cell reference and show the same (desired) information as before. However, if I sort the range A1:J6, the linked cells don't update, but keep their original cell references, and now show different information, which is not what I want.

Any ways around this? How can I sort A1:J6 and have the linked cells update to the new positions of their originally linked-to-cells?

Hope this makes sense and thanks for your help.

Stephen

Answer
Answer

I believe the confusion may stem from you expecting the Sort operation to have the same effect as the Insert Row operation. That just isn't the case... As you point out, when you Insert a row, A7 becomes A8, but when you Sort, A7 remains A7 but its content changes.

As for "...an instant real-time view" I can't think of anything fitting that description which provides faster, more accurate real-time response than right-clicking a cell containing an * & selecting Filter> By Cell Value from the contextual menu.

If you prefer something formulaic, you might take a look at those in the Lookup & Reference category rather than a formula link.

AI: Artificial Intelligence or Automated Idiocy???
Please mark Yes/No as to whether a Reply answers your question.

Regards,
Bob J.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

The obstacle is that a formula link (e.g., =A3) links the cell containing the formula to the cell being referred to, not to the content of that cell. When you sort the list the records are rearranged causing the content of the referenced cells to be different. Long story short :-) this isn't the approach you need to take.

It appears that what you actually want is a restricted listing of those Items which have been starred (*) as Priority. There are a number of possible ways to go about it but unfortunately my time is quite restricted right now. Perhaps another responder will jump in to offer a usable suggestion. In the meanwhile, the simplest way to produce your focused list of Priority Items is to simply Filter the main list based on the criterion of whether the Priority field (column C) contains an asterisk. If you wish, you can then copy & paste the filtered list of Priority Items. For more on using Filters see topic #4 - Filter a List in the Excel 2011 Help article Sort and Filter Lists

AI: Artificial Intelligence or Automated Idiocy???
Please mark Yes/No as to whether a Reply answers your question.

Regards,
Bob J.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated February 24, 2021 Views 10,434 Applies to: