Skip navigation

How To Reference a Cell That Doesn't Change When Sorting

474 Views 14 Replies Latest reply: Nov 2, 2012 3:50 PM by Russ New Boy RSS
Russ New Boy Calculating status...
Currently Being Moderated
Nov 2, 2012 8:50 AM

Hi,

 

Hope you can help.

 

I have created two data tables and want to be able to reference the data in one table from another, which is easy enough.  However, when I manipulate and sort the data in table 2 I want the cell in table 1 to continue to show the contents of, say, cell A2 irrespective of whether the data in that cell has moved through sorting.

 

I thought it would be easy but I have tried using various lookup and offset functions but everytime the cell reference in table 1 changes when I re-sort or delete rows in table 2.

 

What am I doing wrong?

 

Thanks

Russell

iMac, Mac OS X (10.7.3)
  • Wayne Contello Level 6 Level 6 (12,625 points)

    use the offset function in the second table when referring to the first (the sorted).

     

    from the second table:

    =offset($a$1, 1, 0, 1, 1)

     

    from the Numbers built-in help re offset:

    The OFFSET function returns a range of cells that is the specified number of rows and columns away from the specified base cell.

     

    OFFSET

    (base, row-offset, column-offset, rows, columns)

    base: 

    The address of the cell from which the offsets are measured. base is a reference value.

    row-offset: 

    The number of rows from the base cell to the target cell. row-offset is a number value. 0 means the target cell is in the same row as the base cell. A negative number means the target is in a row above the base.

    column-offset: 

    The number of columns from the base cell to the target cell. column-offset is a number value. 0 means the target cell is in the same column as the base cell. A negative number means the target is in a column to the left of the base.

    rows: 

    An optional value specifying the number of rows to return starting with the offset location.rows is a number value.

    columns: 

    An optional value specifying the number of columns to return starting with the offset location.columns is a number value.

  • Wayne Contello Level 6 Level 6 (12,625 points)

    Here is an example of what I suggested:

    Screen Shot 2012-11-02 at 11.39.37 AM.png

     

    In the table on the right:

     

    B1=OFFSET(SortTable :: $A$1, 1, 0, 1, 1)

  • Wayne Contello Level 6 Level 6 (12,625 points)

    Can you state your complete question?  I answered the question you asked.  So you tried that and THEN tried to do something else toward your final goal.  Can you provide an simple explanation what your final goal is?

  • Wayne Contello Level 6 Level 6 (12,625 points)

    Can the formula be amended so that it always references the contents of the cell in the Sort Table relative to its current position but doesn't change that address when Sort table is re-sorted (if that makes sense!)

     

     

     

    see the earlier post where I quoted the Numbers help on this function:

    the the bolded, italicized text

     

    The OFFSET function returns a range of cells that is the specified number of rows and columns away from the specified base cell.

     

    OFFSET

    (base, row-offset, column-offset, rows, columns)

    base: 

    The address of the cell from which the offsets are measured. base is a reference value.

     

    row-offset: 

    The number of rows from the base cell to the target cell. row-offset is a number value. 0 means the target cell is in the same row as the base cell. A negative number means the target is in a row above the base.

    column-offset: 

    The number of columns from the base cell to the target cell. column-offset is a number value. 0 means the target cell is in the same column as the base cell. A negative number means the target is in a column to the left of the base.

     

    rows: 

    An optional value specifying the number of rows to return starting with the offset location.rows is a number value.

    columns: 

    An optional value specifying the number of columns to return starting with the offset location.columns is a number value.

     

     

    so you can use the row-offset and column-offset arguments to as inputs to control which cells, relative to the base, are returned.

     

    if you just wanted to copy the sort table you could:

     

    Screen Shot 2012-11-02 at 12.02.58 PM.png

     

    A1=OFFSET(SortTable :: $A$1, ROW()-1, COLUMN()-1, 1, 1)

     

    select A1 and fill to the right, then select the first row and fill down

  • Wayne Contello Level 6 Level 6 (12,625 points)

    Please post a screenshot of the sort table and a small example (made manually) of what you want the summary table to look like

  • Wayne Contello Level 6 Level 6 (12,625 points)

    try this:

     

    Screen Shot 2012-11-02 at 1.24.17 PM.png

     

    In the table on the right:

    A2=OFFSET(SortTable :: $A$1, ROW()-2, COLUMN()-1, 1, 1)

    B2=IFERROR(IF(A2=A1, "", A2), "")

    C2=IFERROR(OFFSET(SortTable :: $A$1, ROW()-2, COLUMN()-2, 1, 1), "")

     

    select A2 thru C2 and fill down

     

    You may hide column A as it is used for an auxilliary purpose.

  • Jerrold Green1 Level 7 Level 7 (28,185 points)

    Russ,

     

    Any addressing function will work for you. Just stay away from normal cell references. The functions that will generate cell references that are resistant to sorting are:

     

    Offset

    Index

    Indirect

     

    My personal preference for most cases is Index because you can just give it a Table Name for a Range and that allows you to not have to worry about specifying a Cell Range explicitly.

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.