cells not updating from other sheets

Question

cells not updating from other sheets

Situation:

Numbers 3.2.2

OSX 10.9.4

Mac Book Pro, 8GB RAM, 2.5 GHz Intel i5

A spreadsheet file is stored in a dropbox folder and opened locally on a MAC for input.

Multiple people have access to the Dropbox folder but only one person has the sheet open locally.

We use 1 numbers spreadsheet file consisting of 9 sheets.

8 of the sheets only have one table on them (i.e. 1 table per sheet.)

Each table has one or more header cells using a simple Sum(<column>) calculation.

The 9th sheet is a summary sheet which pulls info from the summed header cells on the other 8 sheets.

The summary sheet performs various calculations automatically.

Its a simple price list spreadsheet. Input a qty for various items on the 8 sheets, the header cell sums all the inputs, the summary sheet shows various info for taxes, shipping, etc.

Everything works fine as long as you are going one direction (i.e. entering info for the first time). The trouble comes when you have to change the inputs on the other sheets.

Sometimes the summary sheet won't update with the new amounts from the other sheets. I can force it to update by going to the summary sheet cell, double click like I'm going to change the formula, hit enter...and boom...recalculation is performed.

I've noticed this has been a problem since Numbers 3 came out. Not sure if it was after the 1st or second update.

Has anyone else come up with a work-around for this?

Is there a way to force a sheet to refresh (i.e. a button to put on the sheet for the user to press that executes a "macro" forcing a recalculation)?

This has been very frustrating and potentially costly to our business.

Thanks.

Posted on Sep 19, 2014 11:06 AM

Reply

Answer 1

Best reply

t quinn

Level 5

7,042 points

Sep 19, 2014 5:36 PM in response to natebothewarrior

Hi natebo,

I think they did a thing to ease the computational load so tables don't update until there is a change in them. Are you running the latest version?

You might try NOW() in a vacant corner of your table for a hands off solution or maybe a checkbox click might be enough to trigger a recalculation.

I remember this being more of an issue for me in the past but I am not sure if I changed or the program works differently now.

quinn

Reply

Answer 2

SGIII

Level 8

35,264 points

Sep 19, 2014 6:51 PM in response to natebothewarrior

I don't think Numbers 3 and Dropbox play well together. That could be part of the problem.

If you were to save the document in iCloud then others could collaborate by opening it in their browser via Numbers for iCloud.

SG

Reply

Answer 3

May 26, 2015 3:34 PM in response to t quinn

Thanks Quinn

My summary sheet would not "refresh / recalculate). Entered =now () in a cell and it seemed to trigger a re-calculate.

Thanks again.

Reply

Answer 4

Yellowbox

Level 7

20,347 points

May 27, 2015 1:18 AM in response to natebothewarrior

Hi natebo,

I think that Numbers has changed to reduce the frequency of refreshing calculations. Possibly in response to feedback that recalculation in large documents was slowing data entry.

If quinn's NOW solution doesn't work as often as you want, here is a new improved idea by Wayne. The cell with NOW will refresh each time the checkbox is clicked.

A1 is the checkbox. Click on it whenever you want to change the state of the table (and presumably the whole sheet and document)

Formula in A2

=IF(A1,NOW(),NOW())

If A1 is true, then update NOW, else update NOW. Covered both ways!

A1 is formatted with hour:minute:second so you can see it doing its job.

If that doesn't update the whole document, save, close and reopen.

Regards,

Ian.

Reply