Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sort but Keep Linked *DATA* (XP SP1)

    Can someone tell me if it's possible to maintain linked DATA after Sorting---NOT Linked Cells, e.g.,

    ROW A John Smith $15,000
    ROW B Jan Jones $20,000

    If I have copied Row B so that all of Jan's Data is in a cell at the bottom of my Worksheet, but then I decide that I want to Sort my Spreadsheet Alphabetically, Jan Jones' Data will go to Row A and John Smith's Data will go to Row B.
    In my Linked Row at the bottom, Jan's Data will now become John Smith's Data, which I don't Want.

    Can I keep the DATA Linked instead of the Cells?

    Thank you in advance for taking the time to read and (hopefully [img]/forums/images/smilies/smile.gif[/img] ) reply to this query.

    Brian

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    Use a "lookup" function. VLOOKUP in the dataset with the name. Or a MATCH with the name, coupled with INDEX functions. Make sure you look for "exact matches" which are independent of the order.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    Post Edited -- due to stupid mistake!
    I tried using the column and Row headers.
    It worked fine and I posted it as a solution.
    THEN I hit calc and saw it did NOT work.
    Do not bother with this method....
    Sorry

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    Thanks, except I haven't used VLookup since 1998. Everything I've ever learned about it, I've forgotten! D'oh!

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    It is easy once you get the hang of it.
    I created a simple sample of Vlookups that I have attached.
    If you need more help, please send a sample of the workbook.

    Chuck
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    If you have a lot of "lookups" from 1 row, I would recommend using MATCH to determine the row, and then use INDEX to get the info from all the other columns. It is much faster and less memory intensive.

    Also, If you are a farscape fan, houldn't you be using "frell" instead of "d'oh"?

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    Thanks heaps for the replies. Looking at the great example created, "it's starting to come back to me now".

    However, Excel doesn't seem to be too intelligent about creating these, unless of course I'm doing something wrong. Unless I change the formula to specify Column 2 or Column 3, Excel just copies the EXACT Formula across Columns and down Rows???

    Also, the Data only refreshes when I close and Open the WorkBook. If I try to choose 'Refresh' from the Data Menu, I can't, as it's greyed-out?

    Frell. [img]/forums/images/smilies/smile.gif[/img]

    BTW, Remember to watch Farscape in Record Numbers when it returns to U.S. T.V. in the New Year. We NEED a 5th Season!!!!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    here is an example:
    Put row numbers down col A (2 to whatever)
    Put columns in row 1 (B to whatever)

    in B2 enter:
    =index(DataTable, $A2, B$1)

    When this gets copied through the range (down and across) the relative references will change rows and columns.

    Steve
    PS I don't think the number of viewers matters anymore, Farscape is done.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Sort but Keep Linked *DATA* (XP SP1)

    Thanks, I'll give that a shot.

    RE: Farscape. The 2nd half of Season 4 returns in a few weeks. If it can get a 2.0 rating, there's an excellent chance that Sci-Fi or another channel will pick it up. I'm keeping my fingers crossed, as Farscape is the best thing on T.V.

Posting Permissions

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