How do I sort a table in Excel if it has cell references in it

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

Try this method    Insert a column to the left of the cells to be sorted  Assume cols B G are to be sorted based on B Copy col B Paste values into col A Highlight cols B G Replace   signs with   signs Sort cols A G based on A Highlight cols B G replace   signs with   signs Delete col A  Voila

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I ve had trouble with this myself and found THE BEST and easiest solution      Copy the data which you wish to make into a table and then sort  Paste is somewhere else on your spreadsheet 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  Turn your new data into a table and sort your life away      Hope you find it works  Regards

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I m pretty sure this can be solved with the indirect   function   Here s a simplified spreadsheet               A         B                       C                         D                ------------------------------------------------------ - - - - - - - - -      1  CITY      Q1-Q3 SALES ANNUALIZED SALES  Q1 Q2 Q3  1 33                                                                 - - - - - - - - -      2  Tampa       23 453 00                        31 192 49          ------------------------------------------------------       3  Chicago     33 251 00                        44 223 83          ------------------------------------------------------       4  Portland    14 423 00                        19 182 59          ------------------------------------------------------                                                                    Normally the formula in cell C2 would be  B2 1 33  which works fine until you do a complex sort   To make it robust to sorting  build your own cell reference using the row number of that cell like this    indirect  B  amp row    1 33     Hope that works in your situation   It fixed a similar problem I was having

User · Answer

Put a dollar sign in front of the row and or column of the cell you want to remain constant   Fixed it for me

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I needed to sort cells with references  and really needed to avoid pasting Values to work with   The  Pivot Table  did the trick    Prepare your tables with references  Select the table  with references  and insert Pivot Table In the pivot table  select required filters to make the Pivot table look as your original Table  if needed   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

User · Answer

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

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

Examples related to excel