+ Reply to Thread
Results 1 to 8 of 8

Do you know how to sort a data in Excel if it has cell references in it?

  1. #1
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Cool Do you know how to sort a data in Excel if it has cell references in it?

    Hey Fellow Excel Enthusiasts,

    I created an excel workbook with multiple cell reference throughout the various worksheets. I did this because I was creating a dependent drop down list. When I try to sort my data alphabetically, all my cell name references get changed, and it is quite frustrating. Do you any of you excel experts know how I can sort alphabetically without losing my cell name references?

    Thank You,
    Sajan

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    Hi Sajan,


    when you sort, the cell value is changing its place along-with formula references... is this what you are saying ?

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    Yes I believe so. For example, one reference I have is cornerbooks, which references a2:a5. Do you know how to sort without losing the reference.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    Sort feature will move the data along with references.. its normal.

    Do you want that reference (=formula) should be there but cell value moves ?

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    I want to the reference to move with the correct data. For example, when I sort products and colors alphabetically, the cell reference get all messed up. They reference the wrong data, because I think they stay in the same position, but the data moves. Do you know how I can combat this?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    Could you post a sample of the worksheet so that we can get a clear picture of what you are trying to do?
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    Hey Guys,

    So attached is the spreadsheet I am having trouble with. On the Color Selection, Logo Options, and Foil Cover Worksheets, I have many cell references (if you select a bunch of the same brand, you will see). Now, if I try to sort them alphabetically, all my references get all messed up. Hopefully this elaborates more. Let me know if you need further clarification.

    Thanks for your help!
    Attached Files Attached Files

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Do you know how to sort a data in Excel if it has cell references in it?

    If you are sorting on the columns that have named ranges, the references will be ruined because the names are fixed ranges of contiguous cells (cells that are next to each other in rows and or columns). When you sort, the cells that were together, are no longer in the defined ranges therefore the cell references for the names are wrong.

    Sort the data in the order that you want and then assign the name to the range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1