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

**sajanpatel15** · 06-11-2013, 04:28 PM

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

**dilipandey** · 06-12-2013, 02:48 AM

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>

**sajanpatel15** · 06-12-2013, 02:56 AM

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.

**dilipandey** · 06-12-2013, 03:05 AM

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>

**sajanpatel15** · 06-12-2013, 01:51 PM

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?

**newdoverman** · 06-12-2013, 04:01 PM

Could you post a sample of the worksheet so that we can get a clear picture of what you are trying to do?

**sajanpatel15** · 06-12-2013, 05:50 PM

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!

**newdoverman** · 06-12-2013, 06:56 PM

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.