Formulas looking at Rows created by Forms not Updating

Hello everyone,

I currently have 2 sheets in a workbook that are designed to work together to manage and track the total hours of training groups of employees have completed. The first sheet (Staff Training Logs) uses submitted Forms to populate. The Forms include group, date, hours, attachments, and notes. The second sheet (Staff Training Tracker) looks at the first and gives a running total of training hours completed for each group by using the formula,

=SUMIF(‘Staff Training Logs’!B:B,INDEX(A:A,ROW()),‘Staff Training Logs’!D:D)

When changes are made to (Staff Training Logs), it updates the totals immediately on (Staff Training Tracker). This part works perfectly as intended.

However, when rows are created by Form submissions, the (Staff Training Tracker) sheet does not update until I refresh the entire page. It also does not update when a row is moved to or from the (Staff Training Logs).

Is this a bug? Is there a way to work around this.

In these two pictures, a training for group C was submitted using a form and on the Tracker sheet no hours are updated to reflect the new submission. Once I refresh, it will show the correct number of hours trained for C.

1 Like

Martin - We have tried to reproduce the exact same scenario - but are unable to. Thomas will reach out to you take a look at the issue. In the meantime, would it be possible for you to share the Workbook with murali at spreadsheet dot com ?

I have sent the invite to a copy of the spreadsheet so feel free to play with it as you like. It will not mess up my work. To get the issue to appear, use the form link on the Staff Training Logs, and then go to Staff Training Tracker and you will see that it does not update unless you refresh the browser. Let me know if you need more information.

p.s. I have used both the formula data type and automatic with formulas written in and both have the same issue.

1 Like

Martin, we checked it out. There is a race condition - Tracking this as BUG-005118 - treating with high priority. Will provide updates here.

1 Like

Martin, we are testing out fix for this issue and will be releasing it soon before end of this month.

Additionally, as an alternate fix, request you to consider leveraging relationships in this particular scenario.

We have reworked the sample workbook you have shared to use Relationships & Rollups, request you to review the same.

Relationships & Rollups are also more efficient in comparison with conventional aggregate formulas like SUMIF that always work on an entire range looking for matches.

2 Likes