Sort but Keep Linked *DATA* (XP SP1)

Topic

New Reply

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 ) reply to this query.

Brian

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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!!!!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote