[excel] How do I sort a table in Excel if it has cell references in it?

I have a table of data in excel in sheet 1 which references various different cells in many other sheets. When I try to sort or filter the sheet, the references change when the cell moves. However, I don't want to manually go into each cell and insert $ signs everywhere, because some of the references are continuous, and I may want to auto-fill later.

For example, one of my data columns uses sheet 2, column B 23:28, sheet 2, column C 1:15, and some others. The section of the column which uses sheet 2's column B is all in the same place before sorting, as are each of the other sections. If I insert $ signs, I would no longer be able to insert empty rows and use auto-fill for new data.

In summary, I would like a way to keep my cell references static without using $ signs, but only for sorting/filtering purposes. My current workaround involves copying the values of the table, pasting in an empty sheet, and then sorting. I hope there's a better way to do this.

The answer for me was to separate the data into two grids with a blank column between them. The grid on the left is the data you want to be able to sort, the grid on the right contains the formula you want to calculate. When you sort, the formula simply work on the data in the rows in the left-hand grid and do not get the row references mixed up by the sort.

A very simple way is to keep the problem columns out of the filtered data, but add a column into the filtered section with cell references to them (simply "='cell'").

That way the problem formulas will remain untouched but the filtered data will reference them correctly so you can use those cells to sort accordingly

The easiest method is to use the OFFSET function. So for the original example, the formula would be: =offset(c2,0,-1)*1.33 ="using current cell (c2) as reference point, get the contents of cell on same row, but one column to the left (b2) and multiply it by 1.33"

Works a treat.

create two tabs, one with the formulas and then a second where you paste link the entire table. the second tab should have no problems when sorting!

I've had trouble with this myself and found THE BEST and easiest solution...

1. Copy the data which you wish to make into a table and then sort.
2. Paste is somewhere else on your spreadsheet
3. Values will change due to your cell references, but we don't want that so click the pop-up to do a 'paste special' and choose paste 'values' only.
4. Turn your new data into a table and sort your life away!!!

Hope you find it works. Regards.

this is one good answer to figure out how sorting works from another user and I will add my notes to know how it is not fully correct and what is correct:

The effect on the formula after a sort is the same as copying. A sort does not move the row contents, it copies them. The formula may (or may not depending on the abs/relative references) use new data, just as a copied formula does.

My point is that if the formula can be copied from 1 row to another and the effects don't change, the sorting will not affect the formula results. If the formulas are so complex and so dependent on position that copying them changes the relative contents, then don't sort them.

And my note that I experienced in practice:

The above user is saying right but in fact It has some exception: parts of a columns formula containing sheet name (like sheet1!A1) are treated as absolute references (in spite of copying that changes the references if they are relative ) so that part of formula will be copied without changing references relative to changing the place of formula This includes current sheet cells addressed fully like : sheet1!A2 and will be treated as absolute references(for sorting only) I tested this of excel 2010 and I do not think this issue be solved in other versions. The solution is to copy and past special as value in another place and then use sorting.

Another solution is to: (1) copy the whole table - paste as a link in the same spreadsheet, from now on work only on the 'linked table' (2) copy the column with values to be sorted and paste values only just next to the table you want to sort (3) select the table and replace all = with e.g. #, this will change reference in a static text (4) sort the table by the pasted values (5) replace back all # with =, the references are back Done! It goes pretty fast when using excel shortcuts

I had this same problem; I had a master sheet which was a summary of information on other worksheets in my workbook.

If you just want to filter/sort in a worksheet where you have your data stored, and then return it to its original state (no matter what you are filtering/sorting by) just make your first column a Line Item Number.

After your initial filter/sort you can then just resort by the “Line Item Number” to return everything back to normal. NOTE: This only works if you always add new rows to the end of the list in sequence.

I was hoping to find a why on here, but I think the answer is simpler than we are making it.

The cells should sort regardless of what page they are referencing in your workbook. What was causing the issue for us was any page name references on the current page.

EX: I am in the Today workbook, and I am referencing data for a sales rep on a different page.

If my criteria (The reps name) is in Today!C1 (Or $C1, $c$1) and I try to sort, the sheet will not recognize the action. But if you remove the name that is referencing the page you are on (Redundant reference really) this problem should stop.

So SUMIFS('Sales'!C1,'Sales'!A1,Today!C1) will now be SUMIFS('Sales'!C1,'Sales'!A1,C1)

If someone can enlighten to as why it works that way, that would be amazing.

For me this worked like below -

I had sheet name references in formula for the same sheet. When I removed current sheet name from the formula and sorted it worked correctly.

Best way is to keep your reference data on one side of the sheet and all the formulas on the other side of the sheet. then leave a blank column between them (hide it if you want) an then you'll be sorting only the reference data keeping the Formula references pointing always at the same place. Downside is Excel will recalc everytime you sort.

Easy solution - copy from excel and paste into google sheets. It copies all of your reference cells as absolute cells so you can begin from scratch. Then sort and download back as an excel grid and reformat to your needs.

I used "save as" to copy the sheet to a new file as msdos text format. Doing this removes the formulas, replacing the cell contents with just the computed values. Then open the new file as tab delimited and sort after defining the columns. I needed to sort computed values by an associated text string (destination) for mileage log so that I could sum up the mileage for each destination.

date beginning ending distance destination

where in row 2 and successive rows beginning was the previous row ending and distance was ending minus beginning.

I needed to sort cells with references, and really needed to avoid pasting Values to work with.. The "Pivot Table" did the trick.

1. Prepare your tables with references.
2. Select the table (with references) and insert Pivot Table
3. In the pivot table, select required filters to make the Pivot table look as your original Table (if needed).
4. Sort / filter data further as required.

Just be sure to right click on Pivot table and hit "refresh" each time you change some generic data (used in your tables).

Hope it will help. Andrei

You have a couple options:

(1) There's no way around cell references getting messed up when sorting when not using static references. The most basic way to deal with this is to simply copy and paste as values before sorting, which could be automated via a simple VBA macro.

(2) You could also try utilizing named ranges if you're using a number of common ranges across your formulas. You could define 'Sheet2!B23:28' as 'Range1' and reference 'Range1' within your formulas. In that case, sorting obviously wouldn't affect the range being specified since it's defined elsewhere.

HTH

Not sure if this will satisfy all, but I found a simple workaround that fixed the problem for me. Move all your referenced cells/formulas to a different sheet so that you aren't referencing any cells in the sheet that has the table to be sorted. If you do this, you can sort away!

Even with absolute references, sort does not handle references correctly. Relative references are made to point at the same relative offset from the new row location (which is obviously wrong because other rows are not in the same relative position) and absolute references are not changed (because the SORT omits the step of translating the absolute references after each rearrangement of a row). The only way to do this is to manually MOVE the rows (having converted references to absolute) one by one. Excel then does the necessary translation of references. The Excel SORT is deficient as it does not do this.

Put a dollar sign in front of the row and/or column of the cell you want to remain constant.

Fixed it for me!

We are also struggling with the same issue.

As a workaround, we use a macro to covert table to list, sort the list and then covert the list back to table.

here please find a sample macro

    Sub Sort_Table()

    'change table name to "table1"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _
    "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"


    Dim oSh As Worksheet
    Set oSh = ActiveSheet
  'remove table or list style
   oSh.ListObjects("Table1").Unlist

 'Sort List
  Range("B2").Select
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A4"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:C4")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
 'Change list back to table again
  ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _
    "Table1"
  ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
  End Sub

Try this method:

1. Insert a column to the left of the cells to be sorted.
2. Assume cols B:G are to be sorted based on B
3. Copy col B
4. Paste values into col A
5. Highlight cols B:G
6. Replace = signs with # signs
7. Sort cols A:G based on A
8. Highlight cols B:G
9. replace # signs with = signs
10. Delete col A.
11. Voila!

Append the sheet name to the formula which makes the reference absolute. For example, if the cell reference is =T7 make it =Sheet1!T7. Paste-link would have done the same thing except only when pasting to another sheet. Paste-link does not work as expected if you are pasting in to the same sheet.