Increment cell references

Question

Level 1

46 points

Increment cell references

I have a spreadsheet with daily totals that I want to collate in a weekly sheet. The daily totals are for Monday to Saturday, with weekly totals on the Saturday line.

In a second spreadsheet, I just want the Saturday dates and totals which means the cell reference increments by 6 each time on each line, e.g.

Date Value

Daily::Table 1::A1 Daily::Table 1::D1

Daily::Table 1::A7 Daily::Table 1::D7

Daily::Table 1::A13 Daily::Table 1::D13

and so on. At present I manually copy the cell reference, then edit the value to the correct reference.

What do I do so that the references are incremented? I.e. so the second cell says essentially "6 cells below the cell reference above"? It might be a formula or auto–numbering or auto–referencing.

MacBook Pro 13", OS X 10.11

Posted on Mar 27, 2020 3:41 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Mar 28, 2020 1:08 PM in response to ozRob

Hmmm…

My apologies. I usually check these things before posting, but must have missed that step last night.

Did the test today, and got the same results as yours—the row parts increment by two when the formulas are reproduced two rows below the originals, as I should have expected.

Here's a revised version using INDEX, and some math to get the rows right.

The formula shown is in A2 to the smaller table. The same formula, with the column reference set to D, is in B2,

Daily continues for several rows.

INDEX(Daily::A,(ROW()−2)×6+1)

INDEX returns the value from the nth position in a list. Here the list is column A of Daily, and the position number is determined by the calculation following the comma.

Daily, in the example, contains a list of daynames, starting with Saturday in position 1 (cell A1) and recurring every sixth row after that.

The formula is in row 2 of the smaller table, so ROW() will return 2.

ROW()-2 is 0, multiplied by 6 is still zero, plus 1 is 1—the value needed to retrieve the data from the first cell in column A.

As the formula is filed down column A of the small table, ROW() increments by 1 for each row, x6 expands that one row shift to a six row jump, and we pick up the next Saturday from Daily.

Regards,

Barry

PS: Didn't try the crash test.

What did you mean here: Edit the cell value using option + return:

B.

Reply

Answer 2

Barry

Level 9

60,023 points

Mar 27, 2020 10:42 PM in response to ozRob

With the Daily Table as described your Summary table could be constructed with direct references to the cells that will contain the Saturday dates and the weekly totals

On the summary table, enter these tow formulas

A2: =Daily::Table 1::A1

B2: =Daily::Table 1::D1

Select both cells, then use the Fill handle (small yellow circle) to fill the formulas into row 3.

Edit the cell references in the two copies to A7 and D7.

Now that you have set the initial value and increment for each of the two formulas, select all four cells, then Fill down to the last row of your summary table.

If, in future, you need to extend the table further, drag the row control handle at the bottom of th row reference tabs to add more rows. Check that the formulas in the added rows are following the six-day jumps in date, and if not, select the last two rows of the original table and Fill down as before, overwriting any formulas that have been added automatically.

Regards,

Barry

PS: A bit of Numbers vocabulary:

Spreadsheet or Document are shortened forms of Spreadsheet Document, and refer to the whole docuemnt contained in a single Numbers file.

A 'second spreadsheet' would be a separate document. Numbers does not provide a means of directly accessing a separate document.

A Numbers document contains at least one Sheet, and may contain more than one. A Sheet is a large (theoretically infinitely extensible down and to the right) on which may be placed one or more Objects. Each Sheet has a name. The default name consists of the word "Sheet" and a number. The name may be edited, but cannot match the name of any other sheet in the document.

A Table is an Object, consisting of a number of cells arranged in grid of rows and columns. When created, a Sheet contains at least one Table, but can contain more Tables, or fewer. Each Table has a name. The default name consists of the word Table followed by a number assigned when the Table was created. The name can be edited, and can match the name of another table in the sam Document, but can not match the name of another table on the same Sheet. Giving each Table in a Document a distinct name has the advantage of shortening formulas, as it removes the need for the Sheet name of the Sheet containing that table to be included in the address of cells referenced on that table.

Reply

Answer 3

ozRob Author

Level 1

46 points

Mar 28, 2020 3:19 AM in response to Barry

Thanks Barry, I had previously tried that method and it didn't work. I tried again and got the same incorrect result.

E.g.

Create references as for my OP for the first two cells
Drag down to fill two more cells, the references are now:

Date Value

Daily::Table 1::A1 Daily::Table 1::D1

Daily::Table 1::A7 Daily::Table 1::D7

Daily::Table 1::A3 Daily::Table 1::D3

Daily::Table 1::A9 Daily::Table 1::D9

I can see the logic in how the new references are created, but it's not what I want.

I also named the table in the Daily sheet "Daily", which shortened the references.

I also discovered a foolproof way to crash Numbers:

Set a cell value to a reference to a cell on another sheet.
Edit the cell value using option + return:
1. Double click the reference to make it editable
2. Delete the column part of the reference (so if it's A1, delete the A)
3. Type a new letter (e.g. B or b)
4. Numbers will crash

A work around is to add the new letter so the reference A1 is now say AB1, then delete the unwanted letter to get B1.

Reply

Answer 4

ozRob Author

Level 1

46 points

Mar 28, 2020 8:19 PM in response to Barry

Hi Brian,

that worked, but it's still pretty messy. I was hoping for something like "Get the cell reference above and add 6 to the row index", so that if I change the above cell reference, the below references calculate to a new value too.

Since some weeks have fewer than 6 working days (e.g. public holidays) and having zero values in a table affects averages, I don't include a row for non–working days. That means the constant and the end of your formula needs to change. My real formula looks like:

INDEX(Daily::Date,(ROW()+20)×6+5))

So I have +5 sometimes and +6 other times to allow for missing days. Is there a way to reference the row in Daily::Date rather than the table I'm entering the formula into?

What I'd really like is a function that collates the numbers for a week based on program logic rather than formulas, where the logic is something like "get all the days in week 15 of 2020, create totals for pieces, book and total, then write a row with the date for the end of the week, plus weekly totals for pieces, book and total". Then I just increment the week number for each row in the summary table and I can deal with non–working days elegantly.

I've done similar things in Excel, but really disliked the VBA programming language, it's a horrible mish–mash of syntaxes. I've tried AppleScript previously but struggled as I found it very different to anything I've used before (I'm much better with languages like javascript, Fortran, etc.). Is AppleScript the only programming language for Numbers?

If so, I guess I'd better get back to learning it.

Regarding Option + return, it puts you into edit mode for the cell, just like double clicking only I find it simpler.

Regarding crashes, I've submitted bug report FB7643733.

Reply