+ Reply to Thread
Results 1 to 18 of 18

Cell references skew during sorting

  1. #1
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Cell references skew during sorting

    Hi,

    I'm trying to link a cell in one row to a cell in another row so that the linked-to cell automatically updates when the original cell is changed. Everything I've tried (relative and absolute cell references; link cells; Paste as link, etc.) works fine.....UNTIL I sort the file and the row numbers change. To try to figure this out I created a simplified version of my actual file (see attached). Also, in the first screen capture below, Column B is the Item Name; Column C is a number associated with that item; Column D (highlighted) is the value I'm trying to achieve by linking to the specific cell in Column B. In Columns E-J, I've tried different formulas, and they all give the correct value before sorting:

    Excel-question-1.png

    However, as soon as I sort by Column A, then all the values skew from the desired value in Column D:

    Excel-question-2.png

    I've wracked my brain trying to figure this out. Thanks for any help somebody might have!

    Stan
    Last edited by stan678; 07-14-2019 at 05:38 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Cell references skew during sorting

    Welcome to the forum.

    Convert the entire range into a table, then it should work OK. Failing that, attach the workbook here.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    Thank you - I've uploaded the original Excel file here as you've instructed......
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Cell references skew during sorting

    Your formulae are a bit of a hotch potch!

    Could you please explain what you are trying to achieve in each of the columns with formulae?

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Cell references skew during sorting

    Copy and pastespecial values on column D before sorting.

    Although what you're attempting might be possible with a formula, it would have to be a long and complex one. You might be able to look at a list and compare Audi and Ford as automobile brands, or bull and elephant as animal species, but excel can not do that without being provided with a definition of every possible combination first.

    Even if you do get a formula working, as soon as you add new data which has not been defined, the formula will fail.

  6. #6
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    Sorry, the values shown in Column D are what I'm trying to achieve in each of the Columns E-J (which were my unsuccessful attempts to resolve this issue.) So, after sorting for Column A, I need Row 2 (Audi) to show the value of Column C for Ford (i.e., F-1000), and so on.....

    They're supposed to function as cross-references.....

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Cell references skew during sorting

    OK - in that case, you are probably doomed. Please see Jason's post #5. A database would be better for this. I suspect.

  8. #8
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    This is what I'm afraid of. But it seems like such a simple thing to ask..... hard to believe it's not possible....

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Cell references skew during sorting

    But it isn't simple, is it?

    Jason said this:

    ... but excel can not do that without being provided with a definition of every possible combination first.
    If you just stop and think through the implications of this for a moment, you'll begin to realise what the issue is.

    I think it's a case of using the right tool for the job: the right tool for this is probably Access.

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Cell references skew during sorting

    A simple task with the right tool, unfortunately, excel is not that tool.

  11. #11
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    Quote Originally Posted by AliGW View Post
    But it isn't simple, is it?

    Jason said this:



    If you just stop and think through the implications of this for a moment, you'll begin to realise what the issue is.

    I think it's a case of using the right tool for the job: the right tool for this is probably Access.
    Ok, thank you!

  12. #12
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    Quote Originally Posted by jason.b75 View Post
    A simple task with the right tool, unfortunately, excel is not that tool.
    Right..... thanks!

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Cell references skew during sorting

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Cell references skew during sorting

    The best you will get with excel, is by using an extra column to identify the items in the list that should be linked. You will not be able to do it directly, as you were trying.

    Rather than trying to explain it, I've added it to your sample file (column C).
    Columns E and F are the cross references returned with formulas.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    Thank you for this. When I re-sort this new file by Column A, both Columns E and F disappear.....which I think reaffirms your point that this is not possible.
    Last edited by AliGW; 07-14-2019 at 06:43 AM.

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Cell references skew during sorting

    It doesn't disappear when I sort it, the only logical reason that I can think of which would cause that is if you added more data to the end of the list. As this was only meant as a quick demonstration, the formulas only look at rows 2 to 8, so anything beyond that would be ignored.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Cell references skew during sorting

    Administrative Note:

    Stan - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  18. #18
    Registered User
    Join Date
    07-14-2019
    Location
    Cheyenne, Wyoming
    MS-Off Ver
    10
    Posts
    8

    Re: Cell references skew during sorting

    Got it - thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] First Thread - Sorting and cell references
    By TomW23 in forum Excel General
    Replies: 9
    Last Post: 01-31-2018, 05:57 PM
  2. Sorting with Cell References
    By tangerinezebra in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-04-2016, 11:40 AM
  3. Sorting Table with Cell References
    By Statto in forum Excel General
    Replies: 5
    Last Post: 12-23-2014, 12:32 PM
  4. Sorting messes up cell references
    By Alpha Hunter in forum Excel General
    Replies: 2
    Last Post: 12-28-2010, 04:15 PM
  5. List sorting problem with external cell references
    By bobschwenkler in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-07-2008, 03:39 PM
  6. Sorting Changes Cell References
    By Camp2 in forum Excel General
    Replies: 2
    Last Post: 06-20-2008, 03:41 PM
  7. cell references lost after sorting
    By gert.everaert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2007, 02:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1