Apple's Numbers How to display information in one cell on a sheet, in another cell on a different sheet?

This is the formula that applies to your example tables in the post beginning "see below. I want to, as an example…"

VLOOKUP(TRUE,Unit Identifier Table::$A:$F,5,FALSE)

Parsing the formula above:

The formula is placed in cell K5 of my example table to reduce the need for shrinking the image to accommodate more than 26 columns, plus the lookup table beside it. As all cell references in the formula are to cells on a different table, entering the formula into a different cell on the Checkin table will NOT require changing any of the cell addresses.

VLOOKUP is told to search column A, the leftmost column of six columns (A to F) of a table named "Unit Identifier Table" to find a cell containing the value "TRUE". (a checkbox cell that is checked).

When it finds that cell, the 5 in the formula tells VLOOKUP to return the value in the cell on the same row of the fifth column (E) of the search area (columns A to F of the table named "Unit Identifier Table"

The value in that cell (the text string FPB is returned to the cell containing the formula, and Numbers closes the formula and carries on to the next formula.

Placing this formula in cell X5 of the Checkin table does not require any changes to the formula, but…

If the Unit Identifier Table is not named "Unit Identifier Table" or has fewer than 6 columns, the formula will return an error message.

Constructing the formula:

Click on cell X5 of the checkin table. press = to open the formula editor;

In the editor, type vl

As VLOOKUP is the only Numbers function containing this letter pair, it will immediately appear in the Editor.

Click the shape with that label to confirm that VLOOKUP is the function you want.

The rest of the formula's hint lozenges will be added to the display.

Click on the first lozenge (search-for) and type true

Click on TRUE in the choices menu that appears below the Editor.

The first lozenge will turn darker grey, and be labeled "TRUE"

Click on the second lozenge (columns-range) to select it. The range is on a different table on a different sheet, so…

Click on the tab "PA Unit Identifiers" to open that sheet. (The Formula Editor will follow you there)

Move the pointer to a location near cell A1 on the Unit Identification Table to make the row and column reference tabs appear.

Move the pointer to the tab for column A, press the mouse button, and with the button down, drag right to select all columns from A to the column containing the identifier codes (E), then release the mouse button.*

Click on the next lozenge (return-column). type 5 (the number of the column from which the result is to be returned in the column range selected. (A is 1, B is 2…E is 5).

Click the last lozenge, and select Exact Match from the two-choice menu it contains.

Click the green checkmark to confirm the formula and return to the sheet containing the formula.

*I had some difficulty expanding the column selection from A to A:E. If this also happens to you:

Select column A by clicking on its tab.

Drag the circle that appears at the bottom right corner of the column to the right to add the other columns to the selection, stopping at the column containing the ID codes.

Note that the name of the table (and, if necessary, the name of its sheet) will be automatically attached to the column range,

Regards,

Barry

So to review:

You have a Sheet named "Checkin Sheet" on which there is a single table (Named Table 1, unless you have changed its name).

On this table there is a cell which you want to contain an identification number, obtained from a table (also named Table 1, unless you have changed its name) on a second Sheet named "Agency Identifier".

You want to be able to "set" the value in the cell on Table 1 of Checkin Sheet to the identifiction number of a specific agency on Table 1 of Agency Identifier.

I would start by renaming Table 1 on the first sheet to "Checkin" and Table 1 on the second sheet to "Agency list". This change will allow omission of the Sheet name from references to cells on a second table.

Next I would add a new column to the left of Column A, and format the cells in that column as 'checkbox'.

With that setup, you are ready to use the Agency List as a lookup table for the cell on Checkin (and for other cells on Checkin if you also want to copy the rest of the agency information to that table.

The formula shown below the "Checkin" table assumes only the Agency number will be retrieved and it will come from the second column of the table "Agency List". The error flags are intentional, and show that no Agency has been selected.

When an agency is checkmarked in column A of Agencies list, the display on Checkin changes:

Note also that the formula has been changed. The version shown in the first image is suitable for a single value in a single column.

Replacing the '2', indicating which column contains the value to be returned with 'COLUMN()+1 makes the return column change as the formula is filled across the row holding the original copy, so that each column of Checkin gets a different part of the Agency information, if that is what you need.

To replace the error triangles with 'blank' cells when no row has been checked, use IFEROR as an error trap. I would not add this before checking to see if the formula throws any error messages other than the expected one when no row has been checked.

Formula with added error trap: IFERROR(VLOOKUP(TRUE,Agencies list::$A:$F,COLUMN()+1,FALSE),"")

Regards,

Barry

Use the VLOOKUP formula from my post above, modified to fit the cell locations on your table.

The formula is placed in cell K5 of the Checkin table.

The checkboxes are in column A on the Agencies list table.

The agency identifiers are in column E of the Agencies list table.

VLOOKUP(TRUE,Agencies list::$A:$F,5,FALSE)

In the formula, change : "Agencies list::$A:$F" to "Unit Identifier Table::$A:$F" to match the name of your table.

Regards,

Barry

I suspect it's just Numbers being 'helpful'.

If column A is named "False", and column F is named "Unit name, and there are no other columns with those 'names', Numbers does not need the Table name or the name of the Sheet on which the Table is placed to locate those columns, and drops the sheetname and table name in favour of the two column names.

There is a setting in Numbers Preferences > General to "Use header names as labels" to activate this feature. I leave it turned off (unchecked). I suspect it is active (checked) in your preferences setting.

Regards,

Barry

ASC doesn't accept "attachments". but you can include images in your post.

Here are instructions for taking a screen shot of part of your screen, then posting it here:

To take a screenshot of part of the screen:

• Place the mouse pointer at the top left corner of the area to be captured.

• Press shift-command-4

(The pointer will change to a crosshair)

• Press and hold the mouse button and drag the crosshair to the bottom right corner of the area to be captured.

• When the selection rectangle holds the area you want to capture, release the mouse button.

(You'll hear a 'shutter click,' and the image will be saved as a file on your desktop, with the name Screen Shot, followed by the date and time the shot was taken.)

Your screenshot of a table should be taken while the table or one of the cells in the table is selected, making the table active, and ensuring that the row and column reference tabs are visible. Please include these tabs in any screenshot images.

To insert an image in your post:

• Place the insertion bar at the point you want the image inserted.

(I usually press a return after the line of text I want above the image.)

• Click the 'two mountains' button, second from Right in the set below the message composition window.

(This will take you to a Finder window showing the files on your Desktop. If they are sorted by Date, descending from most recent, your screen shot should be at or very near the top of the list.)

• Double-click the Screen Shot's icon.

(The window will close, you;ll be taken back to your message, and after a short pause, the image will appear in your message.)

• Press return to start a new line below the image, and carry on.

Regards

Barry