9 Replies Latest reply: Jul 3, 2013 12:28 PM by Jerrold Green1
davidmilce Level 1 (0 points)

I am a teacher setting up a tracking system using Numbers for the first time. I have set up a summary sheet with puil information, some of which is "looked up" from other tables within the document. It is working brilliantly until we regrouped the pupils.

 

When i reorganise the students on the summary page I want the same order to follow on all subsequent tables.

However the pupil lists on all subsequent pages stay in the original order!

 

Hope this makes some kind of sense as I am about to throw macbook out of the window!

 

DAve


MacBook Pro
  • Jerrold Green1 Level 7 (29,960 points)

    David,

     

    Sounds as though you've been doing fine up to this point. The problem you are having now is that a reference to a cell will follow that cell if the cell is moved. Normally this is a good thing. If you want to avoid this "feature", a feature of all spreadsheet programs by the way, you need to use one of the alternate addressing schemes.

     

    You could use INDEX, OFFSET, INDIFECT or one one of the three lookup functions, or MATCH, or RANK, to pull the data from a position in the table based on the row and column (or rank) but not by the cell address token such as A1.

     

    If you give us more information about your layout, we may be able to make some specific sugggestions.

     

    Jerry

  • davidmilce Level 1 (0 points)

    This is my front "summary" sheet.

     

     

    This is the second "AF1" sheet which sends data to the AF1 column on the summary sheet I have used the formula =SUMMARY::Table 1 :: A$3 (with various combinations of $) to populate the list of pupils on this sheet from the summary sheet.

    When we regroup them in September I want  to be able to change the order of pupils on the summary sheet and these changes to take effect on subsequent sheets/

  • davidmilce Level 1 (0 points)

    screenshots not posted is this enough info???

    Thanks

    Dave

  • Jerrold Green1 Level 7 (29,960 points)

    David,

     

    Not really enough info to know, for instance, why you are making the A$3 reference absolute in the row part. Here's an addressing form that will get you that address every time, regardless how you may have sorted after writing the reference::

     

    =INDIRECT("SUMMARY::Table 1 :: A3")

     

    This works because the argument is a string that won't be changed by sorting or dragging the target.

     

    Jerry

  • davidmilce Level 1 (0 points)

    Jerry

     

    Thanks that seems to be working BUT!

     

    How can I then make the entire row move along with the name?

     

    Using that fix is solving my original problem but all of the pupils data in that row stays in the original place.

     

    Thanks in hope

    Dave

  • Jerrold Green1 Level 7 (29,960 points)

    David,

     

    Every cell in the row needs its own bulletproof reference to the source. There are ways to simplify writing these references, usually involving the ROW and COUMN functions.

     

    You can post a screen shot with a brief description of your needs. Difficult to help you otherwise.

     

    Jerry

  • davidmilce Level 1 (0 points)

    Here is summary page which acts as the "baseline" for data on all other tables

     

    HScreen Shot 2013-06-30 at 18.40.10.png

    Here is one of the following tables

    Screen Shot 2013-06-30 at 18.40.17.png

    Your fix is working in so much as the pupils names now move accordingly.

    I want all of the tick boxes in each row to move with the pupil name when they are reorganised (ie the unique data in each row stays with the pupi no matter where they go when sorted.

     

    Thanks again

    Dave

  • Yellowbox Level 6 (9,069 points)

    Hi Dave,

     

    A couple of thoughts.

     

    1. Summary Table appears to have 2 Header Rows, the following Table appears to have 1 Header Row. If you can, make the Tables all have the same layout. Then the Row numbers will match.

     

    2. Cells are merged in the Header Row of the second Table. Generally, it is best to avoid merging cells. A bit more work typing the column labels, but again it keeps all Tables with the same layout.

     

    3. (third thought of this couple) when you sort a Table, Numbers keeps each Row intact. It is a bit like a database.

     

    Regards,

    Ian.

  • Jerrold Green1 Level 7 (29,960 points)

    davidmilce wrote:

     

    ...

    I want all of the tick boxes in each row to move with the pupil name when they are reorganised (ie the unique data in each row stays with the pupi no matter where they go when sorted.

     

    Thanks again

    Dave

    Dave,

     

    I might use Match, Offset and Column in my expressions. There are many approaches to this

     

    Match will tell you which row your person's data is in, and OFFSET will then take you to the row indicated by Match and the column indicated by COLUMN().

     

    Let's say you want to access the data for Pupil A. Further assume that the string "Pupil A" is the content of Column A, essentially the title of the record.

     

    MATCH(A, AF1 :: Table 1 :: A) will return the number of the Row in the source table where Pupil A is found.

     

    INDEX(Main, MATCH($A, Main :: $A), COLUMN())  will get the content, in your case the checkbox status.

     

    You are done if you are happy with displaying "TRUE" or "FALSE"

     

    Screen Shot 2013-07-03 at 9.47.39 am.png

     

    But if you want, you can substitute graphics, like this:

     

    =IF(INDEX(Main, MATCH($A, Main :: $A), COLUMN()), "", "")

     

    Screen Shot 2013-07-03 at 9.23.57 am.png

    Regards,

     

    Jerry