transfer cell from one sheet to another, but lock it to that content, no matter how its sorted

Hi Lambo,

"Is there a way to "lock" into a single row to reference so that no matter where it gets sorted, it remains the same on the corresponding sheet?"

"it" appears twice in your question. What is "it" in the first case? What is "it" in the second case?

Do you mean you want the cell on the receiving table to always get its data from the same row (eg. Row 2) of the source table?

INDEX, using a fixed value for the row-index, will do that.

Or do you mean when a sort of the source table moves Joe Smith from row 7 to row 3, you want his data to be displayed on row 5 or the receiving table before the sort AND after the sort?

INDEX, using MATCH to calculate the row-index will do that.

A screen shot of the table supplying the data and one of the table receiving the data plus a description of what's getting sorted and what's to remain the same would help in arriving at a formula specific to your table set.

Regards,

Barry

When you refer to a cell in a formula it'll have a little down arrow on the right of the column/row name. If you touch the arrow a diolag box appears. A $ is used in the referenced cell name indicating it's locked to that cell.

I Sorry. I just did some testing and my solution is NOT what your looking for! It locks it to the cell location and not the data in the cell.

Hi Lambo,

So, as I understand it, you want to have a separate table for each position, and you want to transfer the data in each row of the ALL table to the table for the position.

Not a difficult process, but it will require adding an index column for each position to the ALL table.

Each Table may be on a separate Sheet, if desired, but it is the Table, not the Sheet that will contain the data. Numbers Sheets are, simply put, the blank canvas onto which are placed the Tables, Charts, Graphic object, Text boxes etc. that make up the Spreadsheet document.

Here's a sample, using the data from your 'ALL' table, edited to reduce the typing necessary to produce the table, and with the 'ALL' table renamed "Main" to shorten the display of the formulas. Four of the five 'Position' tables are shown. The fifth is missing only to reduce the space needed for the image.

Columns H-L have been added to provide indices for each of the positions. These columns may be hidden.

The indices are created using the formula below, entered into H4, then filled down to the end of the table, and filled right to column L.

H4: IF($C=H$3,MAX(H$3:H3)+1,"")

The column reference $C remains constant as the formula is filled right; the references to column H adjust to match the column containing each iteration of the formula.

Each of the Position tables contains a single formula, entered in A4, then filled down and filled right to the ends of the columns and rows of that table. The formulas for each table differ only in the two references to the column containing the index values for that position.

PG::A4: IF(ROW()−3>MAX(Main::$H),"",INDEX(Main::$A:$G,MATCH(ROW()−3,Main::$H,0),COLUMN( )))

SG::A4: IF(ROW()−3>MAX(Main::$I),"",INDEX(Main::$A:$G,MATCH(ROW()−3,Main::$I,0),COLUMN( )))

SF::A4: IF(ROW()−3>MAX(Main::$J),"",INDEX(Main::$A:$G,MATCH(ROW()−3,Main::$J,0),COLUMN( )))

PF::A4: IF(ROW()−3>MAX(Main::$K),"",INDEX(Main::$A:$G,MATCH(ROW()−3,Main::$K,0),COLUMN( )))

C::A4: IF(ROW()−3>MAX(Main::$L),"",INDEX(Main::$A:$G,MATCH(ROW()−3,Main::$L,0),COLUMN( )))

The IF part of the formula; IF(ROW()−3>MAX(Main::$z),"",formula)

tests to find if all index values for this position have already been transferred. If TRUE, the formula places a null string ( "" ) in the cell; if FALSE, the formula returns the data from the indexed row.

For details on INDEX, MATCH, ROW() and COLUMN(), see the articles in the Function Browser. To open the browser, click any cell in a table, then press =.

Regards,

Barry

Hi Lambo,

I agree with Barry that an index is the way to go with this. It will update itself when the data in your main table changes.

Here is a solution that only requires a single index column.

The formula in row 2 of the index column is:

C2&COUNTIF(C$2:C2,C2)

This is filled down.

For your report table(s):

In my report table

A3= INDEX(Table 1::A,MATCH($A$1&ROW(cell)−2,Table 1::$F,0))

This is filled down and to the right.

As a little twist I put a popup menu in A1 so this single table can access any position.

If you are devoted to single tables for each position, Row 1 can be eliminated and the formula for my "sg" table

A2= INDEX(Table 1::A,MATCH("sg"&ROW(cell)−1,Table 1::$F,0))

The reference to $A$1 is replaced by "sg" and the row reference ROW()-2 is changed to ROW()-1.

I would add a filter to hide the error rows.

quinn