+ Reply to Thread
Results 1 to 4 of 4

linking rows in two sheets, then sorting data in one, but not the other?

  1. #1
    Registered User
    Join Date
    11-26-2009
    Location
    Up North, Canada
    MS-Off Ver
    Excel 2003 (and 2007 at home)
    Posts
    2

    linking rows in two sheets, then sorting data in one, but not the other?

    Good morning,

    Here's my dilemma:

    I have a list of client data, organized on rows with several parameters in each column (i.e. Column A: Name, B: Address, C: Phone Number, D:Client Number, etc.). I have a master list of all clients in worksheet 1, and a list of clients that owe $ this year in worksheet 2 (not exactly this simple, but this is the idea). I have the info in worksheet 2 linked directly from worksheet 1, so that when I make changes to a client's info on the master list, it's automatically updated on the condensed list-- i.s. so that I only have to do it once.

    Sometimes I need to organize worksheet 1 with the rows sorted alphabetically by name, and sometimes chronologically by client number. However, when I sort worksheet 1, worksheet 2 also sorts, and my list of clients that owe $ this year is no longer accurate.

    How can I have the two worksheets set-up so that the info is linked and updated, but when I sort in one worksheet, the other is not affected? Is this even possible?

    Thanks for thinking about it!

    Cheers.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: linking rows in two sheets, then sorting data in one, but not the other?

    Hi

    In the second, dependent sheet, you would need at least one field per client as an absolute value, say, client ID or name. Then fill the required columns with VLOOKUP based on data from the first sheet

    =VLOOKUP(name,Sheet1!A1:D1000,4,False) or similar

    If you then sort Sheet1, Sheet2 should be unaffected.

    if this does not help, post a small data sample so we can have a look.

  3. #3
    Registered User
    Join Date
    11-26-2009
    Location
    Up North, Canada
    MS-Off Ver
    Excel 2003 (and 2007 at home)
    Posts
    2

    Re: linking rows in two sheets, then sorting data in one, but not the other?

    Ok-- I've never used the VLOOKUP function before (and can't seem to make it work the way I want it to), so I'll attach this sample as an example.

    In Sheet 1, I have the master list of all data. Sheet 2 is all clients that have issues which need to be resolved. But, now I want to sort sheet 1 by File Num, without changing the list in sheet 2-- likewise, when I amend the notes column in one of the sheets, I need it to be reflected on both sheets.

    I tried to use the VLOOKUP function here, but I think it needs some refining in order to work.

    Sorry if my explanation for what I'm trying to do is confusing.

    Thanks for the help!
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: linking rows in two sheets, then sorting data in one, but not the other?

    In the second, dependent sheet, you would need at least one field per client as an absolute value, say, client ID or name.
    I stand by that. In sheet2, column A, do not use a Vlookup but manually enter the File Num. Then Vlookup the rest of the columns. I assume there's already some manual intervention involved deciding which records are shown on the second sheet, because you did not include row 2 and row 5 of the original data.

    Quite a different approach could be to add a status field to your data on sheet1 and then use Data - Filter - Autofilter to filter the original list by status. Then you wouldn't need Sheet2 at all.

+ 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