+ Reply to Thread
Results 1 to 12 of 12

How to bind columns - so whole row is updated when sorting ?

  1. #1
    Registered User
    Join Date
    12-12-2017
    Location
    Planet Earth
    MS-Off Ver
    2013
    Posts
    6

    How to bind columns - so whole row is updated when sorting ?

    Hello

    When I sort the data on the Sheet1 from Z to A. the data on the Sheet 2 column C is not updated.

    Is there a way to solve this?

    I made a sample Excel file & a screenshot.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by MuffinChef; 12-13-2017 at 06:39 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    27,051

    Re: How to bind columns - so whole row is updated when sorting ?

    Because the references in Sheet 2 to Sheet 1 don't change, they just refer to the sorted values. However, the values in column C are static values and completely unrelated to Sheet 1.

    Just won't happen.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-12-2017
    Location
    Planet Earth
    MS-Off Ver
    2013
    Posts
    6

    Re: How to bind columns - so whole row is updated when sorting ?

    Quote Originally Posted by TMS View Post
    Because the references in Sheet 2 to Sheet 1 don't change, they just refer to the sorted values. However, the values in column C are static values and completely unrelated to Sheet 1.

    Just won't happen.
    I understand that. That is why im asking, how to create a relation between Column C & referenced values from Sheet 1 ?

    Any ideas on how to do that?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,481

    Re: How to bind columns - so whole row is updated when sorting ?

    Might need more detail in how you are using this. My first thought, if you want to sort and include the values in column C in the sorting, then the values in column C need to be a part of your main data table in sheet1. Then you can link the column C values in sheet2 to column C in sheet1, just like you already have for the columns A and B.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    12-12-2017
    Location
    Planet Earth
    MS-Off Ver
    2013
    Posts
    6

    Re: How to bind columns - so whole row is updated when sorting ?

    Quote Originally Posted by MrShorty View Post
    Might need more detail in how you are using this. My first thought, if you want to sort and include the values in column C in the sorting, then the values in column C need to be a part of your main data table in sheet1. Then you can link the column C values in sheet2 to column C in sheet1, just like you already have for the columns A and B.
    The simplified example I posted illustrates 100% accurately an issue I am having with a very large collection of tables & multiple sheets.
    Hire are some more details, just like you asked.

    1) Indeed, I want to sort Sheet1 and include the values in Sheet2 -> ColumnC in the sorting.
    2) The values in columnC can not be a part of my main data table in sheet1, it will be unpractical due extremely large table. Data in sheet2-> ColC is updated every day. so if anyone will sort Sheet1 it all goes bad.
    3) Data in Sheet2 -> ColumnC is added manually later. But i cant figure out how to connect it so it would change after sorting.

    If you have any more questions I would be happy to answer.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,481

    Re: How to bind columns - so whole row is updated when sorting ?

    I don't think those three requirements are readily compatible.

    Is it necessary for the sort step to occur on sheet1? The easiest solution that I see (other than adding the data to sheet1) is to add the data to sheet2 and then sort sheet2. Sheet1 remains unchanged. Would that work for you, or must the sort occur on sheet1?

  7. #7
    Registered User
    Join Date
    12-12-2017
    Location
    Planet Earth
    MS-Off Ver
    2013
    Posts
    6

    Re: How to bind columns - so whole row is updated when sorting ?

    Quote Originally Posted by MrShorty View Post
    I don't think those three requirements are readily compatible.

    Is it necessary for the sort step to occur on sheet1? The easiest solution that I see (other than adding the data to sheet1) is to add the data to sheet2 and then sort sheet2. Sheet1 remains unchanged. Would that work for you, or must the sort occur on sheet1?
    Is it necessary for the sort step to occur on sheet1?

    Yes

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,925

    Re: How to bind columns - so whole row is updated when sorting ?

    Why not do everything in sheet 2 then copy columns A&B to sheet 1, overwriting what's there and sort sheet1?

    Incidentally, would you put a meaningful location in your profile. It often helps when we need to consider regional settings, time zones and date formats.

    Why indeed do you need a sheet 1 at all since it's just a subset of sheet2.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,481

    Re: How to bind columns - so whole row is updated when sorting ?

    I don't know that what you want is readily acheived with Excel's built in functionality. In order to bind data together for setting, all of the data needs to be in one list. If you cannot add the data to sheet1 before sorting or sort sheets separate from sheet1, then I don't readily see a way to do this across the two tables as you have them now.

    Would you be willing to add a third table? I could see doing something where you add your new data to a third table in sheet3 (A and B not linked to sheet1). Then column C of sheet2 can use a lookup function with this table in sheet3 as the source table for the lookup function. Would that be something workable?

  10. #10
    Registered User
    Join Date
    12-12-2017
    Location
    Planet Earth
    MS-Off Ver
    2013
    Posts
    6

    Re: How to bind columns - so whole row is updated when sorting ?

    Quote Originally Posted by MrShorty View Post
    I don't know that what you want is readily acheived with Excel's built in functionality. In order to bind data together for setting, all of the data needs to be in one list. If you cannot add the data to sheet1 before sorting or sort sheets separate from sheet1, then I don't readily see a way to do this across the two tables as you have them now.

    Would you be willing to add a third table? I could see doing something where you add your new data to a third table in sheet3 (A and B not linked to sheet1). Then column C of sheet2 can use a lookup function with this table in sheet3 as the source table for the lookup function. Would that be something workable?
    There are too many tables. It is already hard to navigate, therefore I was looking for more elegant solution rather than adding more tables & making things even more confusing.

    Okey maybe an VB script will do the trick ?

    The question is now when will the VB script execute?

    For example I sort Sheet1, activate Sheet2 (now the vb script should run & store the ColumnC in an array), now the Sheet2 is sorted & now VB script is run again to put ColC values in proper places.

    Would that be possible or the VB script runs after all sorting is done?

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,925

    Re: How to bind columns - so whole row is updated when sorting ?

    If a VBA macro is permitted then I'd simply use the method I described in post #8

    There are then just four lines of code
    1. Sort Sheet 2
    2. Clear exiisting contents of sheet1
    3. Copy columns A&B from Sheet 2 to sheet 1
    4. Sort sheet 1

  12. #12
    Registered User
    Join Date
    12-12-2017
    Location
    Planet Earth
    MS-Off Ver
    2013
    Posts
    6

    Re: How to bind columns - so whole row is updated when sorting ?

    Oh my, well too much thinking results in overthinking.

    Solution was to just vlookup to populate the data on the sheet2, this way if you sort Sheet1, Sheet2 stays unaffected.

    Problem solved.

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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