Syncing Sheets in Numbers

PS: Regarding entering formulas, you wrote:

When I first tried it it wouldn't work and I think that was why I couldn't get the instructions to work ... just kept getting a = sign appear in the cell. Now I try it and it works just fine - I must have been doing something else that interfered with the function.

To enter a formula from the keyboard:

Example formula: =Table 1::A1+1

The example, entered on any cell in a table that is not "Table 1" will return the value in cell A1 of Table 1, and add the value 1 to it.

Procedure:

On Table 2,

• click any cell to select it.
• press = to open the Formula Editor (This will also open the Function browser in the right sidebar.)
• Type the formula ( Table 1::A1+1 ) in the editor. (note that you do not include the leading = here)
• The cell reference (Table 1::A1) will change to a token (sometimes referred to as a 'lozenge') as shown in the image below.
• Click the green checkmark to confirm the entry.

The formula should look like the one in the editor in the example.

The example also shows results from the same formula filled down to B2 and B3 of Table 2.

(A1, A2 and A3 in column A are labels telling which cell of Table 1 is being referenced in the formula in that row of column B.)

Note that attempting to add 1 to the letter B (in Table 1::A2) returns an error, while adding 1 to a date (row 3) returns the next day's date.

Regards,

Barry

Hi David,

In my procedure, no cells is copied from Booking 2017 table. Actually, you shouldn't.

Just use reference to them in formula for cells on Cleaner List table.

David Graeme-Baker wrote:

Creating a formula for each cell would take me an age

Typing formula is required only for A1 and E1. Rest of the table can be populated from them.

With simple description, do the below on Cleaner List.

1. Set reference formula for A1 and E1. ( A1 = Booking 2017::A1 , E1 = Booking 2017::K1 )
2. [Copy] cell A1 and [Paste] to B1:D1
3. [Copy] whole row 1 and [Paste and Match Style] to the rest of rows

( I found some typing mistake in previous post, but you can see them easily ).

# Replace to `1' with `2' if no needs to set for header row.

If you want the header row filled manually, follow the steps below ( more detailed than previous one )

On Cleaner List table,

1. Set formula for A2 as, Booking 2017::A2 ( type '=' key, click A2 on Booking 2017 table, then type return key )
2. Set formula for E2 as, Booking 2017::K2 ( type '=' key, click K2 on Booking 2017 table, then type return key )
3. Select A2 cell
4. do [Copy] ( command-C )
5. Select B2:D2
6. do [Paste and Match Style] ( option-shift-command-V )
7. Select whole Row 2 ( in other words, A2:E2 )
8. do [Copy]
9. Select all rows except the header row ( shift-command-down arrow, while A2:E2 selected )
10. do [Paste] ( command-V )

Does this make sense now?

Hi David,

I'm assuming your Cleaning List, printed this week, should not include the bookings from January, as shown on the table.

Here's a solution that can be adjusted to determine the number of weeks to include on the list. The example is set to list four weeks, starting on the date in Cleaning List::B1:

The solution uses an index column (L) on Bookings 2017 to create an index of the rows whose data will be transferred to Cleaner List. This column is needed by Numbers, but should be hidden from view to prevent accidental damage to the formulas in its cells.

The Index is created by this formula, entered in Bookings 2017::L2, and filled down:

IF(AND(C≥Cleaner List::B$1,D≤Cleaner List::D$1),MAX(L$1:L1)+1,"")

The formula checks the Start Date in column B and the End Date in column C. If the Start date is on or after the corresponding date in B1 of Cleaner List, AND the End date is on or before the corresponding date in D1 of Cleaner List, then the formula adds 1 to the largest value in cells above it in its column, otherwise it returns a null string ( "" ).

Cleaner List uses two formulas:

The first is entered in cell D1, and calculates the date 4 weeks after the date entered in B1:

B1+28

To change the number of weeks, change the multiple of 7 added to B1.

The second is used to transfer the indexed data from Bookings 2017 to the corresponding cells in Cleaner List.

The formula is entered into cell A3:

IF(ROW()−2>MAX(Bookings 2017::$L),"",INDEX(Bookings 2017::$A$1:$K$11,MATCH(ROW()−2,Bookings 2017::$L,0),MATCH(A$2,Bookings 2017::$1:$1,0)))

Because A is a Header column, the formula cannot be filled across the header/body boundary between columns A and B. It can, however be filled using a single copy-paste process:

• After clicking the green checkmark to confirm the formula in A3, and with the cell still selected, press command-C to Copy.
• Then shift-click on cell E7 to extend the selection to all cells in rows 3-7.
• Paste.

The core part of this formula is the INDEX function, shown in bold.

INDEX(range, row-index, column-index, area-index)

The range is all of Bookings 2017, except column L.

row-index is the number returned by the first MATCH. The number is the place in column L of the index number matching ROW()-2 (for A3, ROW() is 3, and the index number is 3-2, or 1 found in the index column row corresponding to Mr. Banks (row 4).

column-index is the number returned by the second MATCH, matching the location in the list in row 1 of Bookings 2017 of the value in cell A2 of Cleaner List. For A3, this is the "Name" column.

INDEX returns the value from the cell at the intersection of column 1 and row 4.

The first part of the formula is an IF statement that prevents the INDEX calculation in rows beyond the last index value.

Regards,

Barry

"My word Barry - that sounds a bit beyond me !

I'm just coming to grips with Numbers (in fact with spreadsheets at all)."

Hi David,

I can understand that.

To_Mi's solution has the advantage of using a very simple formula to transfer the data from Bookings 2017 to the same location on Cleaner List.

Mine does the same thing, but offers the option of restricting the data transfer to a chosen range of dates—and that's where the complications arise.

The tables in this example—Cleaner List-1 and Cleaner List-1-1 are duplicates of Cleaner List, using To_Mi's simpler formula to transfer the data from Bookings 2017. The information below deals with controlling which rows will be displayed (and printed) from Cleaner List

There are other methods of controlling the data displayed on the printed copy:

1 Manually hide the rows you don't want to print.

On Cleaner List:

• Click any cell to make the table active.
• Select the rows before the ones you want to print.
• Go Table (menu) > Hide Selected rows*
• Select the rows for dates after the ones you want to print.**
• Go Table > Hide selected Rows.

After printing:

• Click anywhere on the table to make it active
• Go Table > Unhide all rows.

*(You can use the Table menu in the menu bar or the contextual menu opened by placing the mouse pointer near one of the selected rows' reference tabs and clicking the wide v that appears between the tab and the table.)

**(An alternate is to add these rows only when they are needed. Numbers will automatically fill the formulas into these added rows.)

OR

2 Filter the Cleaner List table on Column C (Start Date)

On Cleaner List:

• Click on any cell to activate the table
• Click the sort and Filter button, then choose Filter to open the Filter panel.
• Click Add a Filter, then choose column C (Start Date)
• Click Dates, then scroll to the bottom of the list and choose 'range'
• Enter the Start dates for the first and last row to be shown,
• Click the checkbox beside "Filter" to activate the filter.
  

Result with filter active:

OR

3 Use a filter that can be turned on and off without opening the filter panel.

This one requires adding a checkbox, a 'helper' column and a formula (somewhat simpler than the one above) to Cleaner List.

On Cleaner list:

• Format Cell E1 as a Checkbox cell to be used to Filter the table when checked and to 'show all' when unchecked.
• With E1 still selected, press option-right arrow to add a column to the right of E
• Click on E3, press = to open the Formula Editor, then enter the formula below, and fill down to the end of the column (notes on the formula below):
  IF(E$1,AND(C3≥B$1,D3≤D$1),TRUE)
  Initial results (checkbox unchecked)
  
  Checkbox checked (filter inactive)
  
  Column F (show) hidden, Filter active, checkbox checked
  
• Once set up, the checkbox (and the dates in B1 and D1) control whether the full table or only selected rows are displayed (and printed), with no need to open the filter panel.

Notes on the formula:

shown for cell F3

The purpose of this formula is to return TRUE to each row that is to be shown and FALSE to each that is not to be shown.

Syntax for IF: IF(test,do-if-true,do-if-false)

E1 (the checkbox) is the test, Checked (true) means 'filter the table')

If E1 is TRUE, IF passes control to the next section.

AND tests two statements: "the date in C3 is on or after the date in B1" and "the date in D3 is before or on the date in D1". AND returns TRUE if both statements are true or FALSE if either is not true. IF places this result in the cell.

IF Ei is FALSE, IF skips over do-if-true, collects the fixed value (true) there, and places this result (TRUE) in the cell.

The filter rule, which is alway active, tells the table to show only the rows containing TRUE in column F.

Regards,

Barry

Hi David,

This is my solution, assuming,

• Table names are "Booking 2017" and "Cleaning List"
• Both tables start with column A, row 1.
• There is no hidden column or row.
• Both tables have the same number of rows.

On Cleaner List

[1] Set header cells

A1 = Booking 2017t::A1

# type "=", then click A1 on Booking 2017 table.

B1 = Booking 2017::B1

C1 = Booking 2017::C1

D1 = Booking 2017::D1

# you can copy cell A1 and paste to B1~D1

E1 = Booking 2017::K1

[2] Select whole row 1 ( click 1 on row bar )

[3] Do 'Copy' ( command-C )

[4] Select all cells ( command-A )

[5] Do 'Paste and Match Style' ( option-shift-command-V )

If you have more row on Cleaner List table than on Booking List table, the extra rows will show error due to the invalid reference to cells that don't exist.

You can also have another table to check if both have the same number of rows.

The formula for the checking table will be something like,

A1 = ROWS(Booking List::A)-ROWS(Cleaning List::A)

B1 = IF(A1=0, "OK",IF(A1<0,"Cleaning List has extra "&ABS(A1)&" row"&IF(A1<-1,"s",""),"Cleaning List needs "&A1&" row"&IF(A1>1,"s","")))

Hope this works for you.

You refer to typing "=" to create a formula - is that correct ?

Yes, giving '=' as the first character of the cell content is the way to tell Numbers that it is a formula not value.

If you want a text string starting with '=' as a cell value, put a single quote (') in the beginning of the text.

For example, select a cell and type "'=A" ( without double quotes ) and hit return key. The cell shows its value as "=A" ( without double quotes ).

This works on all spreadsheet applications as I know.