Highlight both columns A and B together (or better yet, the whole row), with column A as your primary sort key.
When you sort, you USUALLY want to sort the whole row anyway, not just a column, for the reasons you explained.
This is a discussion on How do I sort data, but keep a formula in correlated cell?? within the Excel Questions forums, part of the Question Forums category; For example, in Column A I have a list of Names. In Column B I have a formulas that references ...
For example, in Column A I have a list of Names. In Column B I have a formulas that references other cells to calculate a value associated with the Name in Column A. If I sort Column A alphabetically, the Names in Column A are rearranged, but the associated formulas in Column B do not move - so my values in Column B no longer match the correct name. How do I sort and keep the formulas associated with the Name? THANKS!
Highlight both columns A and B together (or better yet, the whole row), with column A as your primary sort key.
When you sort, you USUALLY want to sort the whole row anyway, not just a column, for the reasons you explained.
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
Thanks, that helps! But the formulas in Column B are linked to other Worksheets and Workbooks. So, I still have the same issue. Any other ideas?
What kind of formulas are they that sorting causes them problems? If you post the formulas which are giving you trouble, we may be able to find a way to work around it.
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
The formulas are straight forward links to other cells. For example for Name A1 the formula in B1 is a link to a number in a different Worksheet. But when I sort a new name is placed in A1, but the link in B1 has not changed. The result is that the new Name in A1 is next to the value that used to be associated with the old Name in A1.
Is it a formulaic link (like Sheet2!A1) or a Hyperlink? If is just a formula, please post it.
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
Formulaic Link: ='[Markets - Motherboards.xls]Motherboard Summary'!B9
In this other Work book there is a number that feeds into the cell B1.
Thanks!
I don't understand. If you highlight your WHOLE range (trying selecting all the entire row, not just columns A and B), and sort by column A, the values in column B will move with column A so the correlation is still there.
Is there something more to this you forgot to mention? Are formulas related to other formulas in different columns?
Perhaps if you could post a section of your worksheet, we could see what is going on.
Check out "Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board." at the bottom of the page to post sections of your worksheet.
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
Sorry for bringing back such an old thread but I have a similar problem.
I am trying to sort some data. Columns D through AJ.
I am sorting based on Column D, which contains an index match, looking up an account number on a different sheet, based on the value in Column E.
Column E is using data validation, drop down list, but I don't think this should effect my result.
When I sort my data based on Column D, the reference to Column E (inside my index/match formula in Column D) does not sort properly. For example the formula in D1 should reference E1, but when I sort, D1 references E5 (for exmaple)
To further complicate this, I am doing all my sorting through a Macro, and the problem only appears to be happening half way down my list.
Like this thread? Share it with others