Dynamic table of workbook sheets/cell values

Question

Dynamic table of workbook sheets/cell values

As part of our internal Scrum "process" I need to produce documentation for some ISO certification stuff.

So, every sprint start I need to produce a Sprint Planning Document and at the end of the Sprint, a Sprint Report.

He have template for this that were initially produced in Excel, but we've moved them to Numbers, since everyones on Mac now.

I change the file in order to have more info and extract some insight. I've originally made it on Google Sheets, and using javascript scripts I was able to automate everything I needed and produce dynamic graphics and tables. However, due to security policies, I had to remove everything from Google and use only local files, on Numbers and I'm not being able to produce this reports automatically.

My current file in numbers have, for each Sprint, three tables:

One for Sprint Planing (left)
One for Sprint Report (right)
One for Summary (bottom)

This Sheets have other tables with all the items from were I'm getting some other data/values.

For each Work Sprint I create a new sheet named "Sprint + (number of sprint)" by duplicating a template sheet that I also have on the same Numbers document.

I start by Filling in the green cells on the Summary table that auto-fill the required fields on both reports with similar values.

What I need and I'm not being able to do is to have a "Totals" Sheet with dynamic table that automatically fills in the values for :

Commited Scope
Delivered Scope
Missed Scope
etc...

every time I create a new "Sprint" Sheet. Since there's some limitations in Numbers compared to Google Sheets, I'm considering create a new line on this table manually for every sprint, entering only the sprint number on some column and then Numbers would go to the appropriate Sheet to pick up the values.

I've tried Apple Script using Javascript but the documentation is so poor that I couldn't achieve anything...

Is it possible to achieve this? Any direction would be appreciated.

MacBook Air Apple Silicon

Posted on Aug 31, 2022 8:59 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,024 points

Sep 5, 2022 3:22 AM in response to Nelson Jerónimo

If you use a comma, not a point, as a decimal separator then you need to use semicolons, not commas, to separate the parameters in functions. You did not have any decimal numbers in your screenshot but I now see your dates are formatted as DD-MM-YYYY and those two things often go together. Sorry.

B2 =IFERROR(INDIRECT($A2&"::Table 1::"&B$1);"")

Reply

Answer 2

Dorain1

Community Specialist

Sep 1, 2022 12:11 PM in response to Nelson Jerónimo

Hi Nelson Jerónimo,

Let's see if the link below can help with the steps for calculating data. It's found in the Numbers User Guide for Mac - Apple Support.

Calculate values using data in table cells in Numbers on Mac - Apple Support

With the user guide link, it should be a helpful resource with locating most questions you have about working with Numbers.

Good luck!

Reply

Answer 3

Badunit

Level 7

28,024 points

Sep 1, 2022 6:35 PM in response to Nelson Jerónimo

If I understood the problem, you have a number of sheets with names like Sprint1, Sprint2, Sprint3, and so on. All of those sheets have a table that has cells for Committed Scope, Delivered Scope, and so on. You want a "Totals" sheet that totals up each of those values from all the Sprint sheets.

In Excel, if all the worksheets are together (no other sheets intermixed), you can make a formulas that will sum up a specified cell from a range of sheets, such as summing cell B2 from all the Sprint sheets. Numbers does not have that capability. What you can do in Numbers is to create a table that gets each of those values then sums them up. Here is an example

Formula in cell B2 =IFERROR(INDIRECT($A2&"::Table 1::"&B$1),"")

Fill to the right and down to the bottom to complete the three columns

The final row is a footer row.

The formula in B13 = SUM(B)

Fill right to do the other two columns.

You pre-fill column A with the sheet names, including those that do not yet exist. As you create those new sheets their data will populate the table.

If you need data from different tables from those sheets, instead of just the cell names B2, B3, B4 in the header row you can include table names like Table 1::B2, Table 2::B3, Table 1::B4. The formula would then be

B2 =IFERROR(INDIRECT($A2&"::"&B$1),"")

Reply

Answer 4

Sep 5, 2022 4:28 AM in response to Badunit

Sorry, I've figured it out in the meanwhile... the problem was that I was not using the cells on the header to 'compose' the address o the cell I was trying to get the value from. I'm good now. Thanks a lot!

Reply

Answer 5

Sep 2, 2022 1:33 AM in response to Dorain1

Well I think I have those basics covered. The problem was a bit more complex, but hey, thanks for taking the time to help. Thank you very much.

Reply

Answer 6

Sep 2, 2022 1:34 AM in response to Badunit

This may be very helpful! I'll give it ago. Thank you very much.

Reply

Answer 7

Sep 5, 2022 2:48 AM in response to Badunit

It keeps giving me a syntax error (The formula contains a syntax error). I even copied your formula and created sheets and tables accordingly just to test it... Any ideas?

Reply

Dynamic table of workbook sheets/cell values

Similar questions