| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort but keep linked formulas?
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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas?
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 "GovUser" skrev i en meddelelse ... 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas?
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" wrote: 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 "GovUser" skrev i en meddelelse ... 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas?
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" skrev i en meddelelse ... 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" wrote: 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 "GovUser" skrev i en meddelelse ... 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas?
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... "Hans Knudsen" wrote: 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" skrev i en meddelelse ... 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" wrote: 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 "GovUser" skrev i en meddelelse ... 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas?
I am not quite sure I understand. Could you possibly (in private mail) send me a workbook with an example?
Hans "GovUser" skrev i en meddelelse ... 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... "Hans Knudsen" wrote: 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" skrev i en meddelelse ... 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" wrote: 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 "GovUser" skrev i en meddelelse ... 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas? - Thanks!!
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!! "Hans Knudsen" wrote: I am not quite sure I understand. Could you possibly (in private mail) send me a workbook with an example? Hans "GovUser" 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... "Hans Knudsen" wrote: 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" 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" wrote: 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 "GovUser" 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sort but keep linked formulas? - Thanks!!
My pleasure.
Hans "GovUser" skrev i en meddelelse ... 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!! "Hans Knudsen" wrote: I am not quite sure I understand. Could you possibly (in private mail) send me a workbook with an example? Hans "GovUser" 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... "Hans Knudsen" wrote: 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" 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" wrote: 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 "GovUser" 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) -------- 1 XYZ xxx (new =A5, wanted "RST" (original #5)) 2 RST xxx (new =A4, wanted "ABC" (original #4)) 3 GHI xxx (new =A2, wanted "GHI" (original #2)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif formulas change after doing a sort | Excel Worksheet Functions | |||
| dynamic sort macro across 3 linked sheets | Excel Discussion (Misc queries) | |||
| How to sort and keep formulas | Excel Worksheet Functions | |||
| How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
| How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
