Results 1 to 8 of 8

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

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

  1. #1
    New Member
    Join Date
    Jul 2003
    Posts
    4

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

    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!

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default 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.
    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!"

  3. #3
    New Member
    Join Date
    Jul 2003
    Posts
    4

    Default 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?

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default 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.
    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!"

  5. #5
    New Member
    Join Date
    Jul 2003
    Posts
    4

    Default 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.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default 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.
    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!"

  7. #7
    New Member
    Join Date
    Jul 2003
    Posts
    4

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

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default 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.
    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!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com