How to sort but keep linked formulas?

G

Guest

I have a complete set of UNsorted, source data. I have created a SUBSET of
this data by LINKING and copying the desired ROWS.

My question: When I SORT the source set/rows of complete data (by name in
column A), the links in the subset remain linked to the CELL references in
column A, not the data. Therefore, the original copied data gets replaced
with the new sorted data. I have tried the links with and without absolute
cell references. I do not want to use "Paste Values" because the source data
may change and I want the copied subset to update automatically.

Is there a way to keep the LINKS to the original client rows of data in my
subset, while also being able to SORT the Source data?

***********
EXAMPLE
***********
Complete Source (Before sort)
--------
1 XYZ xxx
2 GHI xxx
3 LMN xxx
4 ABC xxx
5 RST xxx

Linked subset data (Before sort)
--------
1 RST xxx (cell formulas: =A5 or =$A5)
2 ABC xxx (cell formulas: =A4 or =$A$4)
3 GHI xxx (cell formulas: =A2 or =$A$2)


***********
***********
Complete Source (After sort)
--------
1 ABC xxx (previously 4)
2 GHI xxx (previously 2)
3 LMN xxx (previously 3)
4 RST xxx (previously 5)
5 XYZ xxx (previously 1)

Linked subset data (After sort)
 
H

Hans Knudsen

If you have your data in A1:B5 and your linked data for example in A12:B14 then do as follows:

Enter in A12: =INDIRECT("A"&ROW(A5))
Enter in B12: =INDIRECT("B"&ROW(A5))

Similarly for RST and GHI.
Now you can sort A1:B5.

Hans
 
G

Guest

Thank you for your reply. I tried the formula. Unfortunately I got the same
result - my linked subset continued to reference the original CELL locations,
but the data in those original cells has now changed due to the sort.

I believe the linked cell references are not updating because I am Sorting
the original source data as opposed to moving cells by Cutting or Inserting
/Deleting. Hence that may be why the INDIRECT function didn't work???

Hans Knudsen said:
If you have your data in A1:B5 and your linked data for example in A12:B14 then do as follows:

Enter in A12: =INDIRECT("A"&ROW(A5))
Enter in B12: =INDIRECT("B"&ROW(A5))

Similarly for RST and GHI.
Now you can sort A1:B5.

Hans
 
H

Hans Knudsen

My fault. Sorry!
What then if you instead try these two in A12 and B12
=VLOOKUP("RST";$A$1:$B$5;1;0)
=VLOOKUP("RST";$A$1:$B$5;2;0)
and similarly for the others replacing "RST" by "ABC" and so on?

Hans



GovUser said:
Thank you for your reply. I tried the formula. Unfortunately I got the same
result - my linked subset continued to reference the original CELL locations,
but the data in those original cells has now changed due to the sort.

I believe the linked cell references are not updating because I am Sorting
the original source data as opposed to moving cells by Cutting or Inserting
/Deleting. Hence that may be why the INDIRECT function didn't work???
 
G

Guest

Hans, Thank you - that worked!!! I had thought about VLOOKUP but I couldn't
figure out what to really do with it...

So now when I sort the original source data, my linked subset *does* keeps
referring to the original data. One more question.

The company name in is column A and the VLOOKUP formula is great to keep
that data ( =VLOOKUP("CompanyName",$A$1:$A$5,1,0)). But the rest of the row
is data related to the company e.g. sales in FY04, FY05, FY06. How can I
keep that data also? I can't insert a fixed sales $$ using VLOOKUP because
the entire reason I didn't want to Paste Special, Values was to maintain the
ability to update the data...

Maybe this whole thing (linking cell references) just doesn't work with
Sorting rows. Only Cutting and Inserting/Deleting rows...
 
H

Hans Knudsen

I am not quite sure I understand. Could you possibly (in private mail) send me a workbook with an example?
Hans
 
G

Guest

My email to you (I clicked on your name) bounced back but I think I finally
understand what you really meant now! I was using the VLOOKUP wrong.

I thought I needed to change the “lookup_value†(the item in quotes) for
each cell. I finally GET IT that all I need to do is “look up†my company
name in the first column and then use the VLOOKUP formula to return the
values from the remaining columns in that row. Thank you so much for taking
the time to assist!!
 
H

Hans Knudsen

My pleasure.
Hans

GovUser said:
My email to you (I clicked on your name) bounced back but I think I finally
understand what you really meant now! I was using the VLOOKUP wrong.

I thought I needed to change the "lookup_value" (the item in quotes) for
each cell. I finally GET IT that all I need to do is "look up" my company
name in the first column and then use the VLOOKUP formula to return the
values from the remaining columns in that row. Thank you so much for taking
the time to assist!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top