How do I sort data, but keep a formula in correlated cell??

michels24

New Member
Joined
Jul 24, 2003
Messages
4
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Re: How do I sort data, but keep a formula in correlated cel

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.
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

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?
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

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.
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

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.
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

Is it a formulaic link (like Sheet2!A1) or a Hyperlink? If is just a formula, please post it.
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

Formulaic Link: ='[Markets - Motherboards.xls]Motherboard Summary'!B9

In this other Work book there is a number that feeds into the cell B1.
Thanks!
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

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.
 
Upvote 0
Re: How do I sort data, but keep a formula in correlated cel

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.

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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top