You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: Nelson Jerónimo
Nelson Jerónimo Author
User level: Level 1
4 points

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
Question marked as Top-ranking reply
User profile for user: Badunit
Badunit
User level: Level 7
28,234 points

Posted on Sep 5, 2022 3:22 AM

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);"")

View in context

Similar questions

7 replies
Question marked as Top-ranking reply
User profile for user: Badunit
Badunit
User level: Level 7
28,234 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
User profile for user: Dorain1
Dorain1
User level: 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
User profile for user: Badunit
Badunit
User level: Level 7
28,234 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

Dynamic table of workbook sheets/cell values

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up