Better solution to force recalculate

Question

Level 5

5,047 points

Better solution to force recalculate

I have searched and found nothing that worked for me. I am making a character sheet creation spreadsheet (roll dice for characteristics).

the other suggestions didn't work (check boxes, formulas on same table, etc...)

I did find something that seems to work, but it seems hokey.

I put a check box in a cell in a table i called Recalculate. Then modified the existing formula thus:

=(RANDBETWEEN(1,6)+RANDBETWEEN(1,6))×(Recalculate::$A$1=Recalculate::$A$1)

It seems I have to make numbers think that the formula i want to recalculate (random number) is somehow dependent on the checkbox, but the checkbox is simply always returning multiply by 1.

I know some of you are way smarter than me on Numbers, any ideas on something less hokey/workaround-ish that doesn't require me to change every formulas i want to recall every time?

I did submit feedback asking for random functions to be on a recalculate every time numbers recalls any cell.

Thanks

Jason

iPad, iOS 8.1.3, iPad, macMini, iPhone 5s and 5c

Posted on Jul 2, 2015 5:20 PM

Reply

Answer 1

Yellowbox

Level 7

20,383 points

Jul 2, 2015 6:00 PM in response to jaxjason

Hi Jason,

I tried your formula and got a blue warning triangle (a boolean value instead of a number).

This is based on an idea by Wayne. If the checkbox is "true", then carry out the random function, else (checkbox is 'false") carry out the random function. Gotcha!

Formula in B2 of Table 1 (and Fill Down)

=IF(Recalculate::$A$1,RANDBETWEEN(1,6)+RANDBETWEEN(1,6),RANDBETWEEN(1,6)+RANDBET WEEN(1,6))

Click on the checkbox

Regards,

Ian.

Reply

Answer 2

Hiroto

Level 5

7,461 points

Jul 3, 2015 1:19 AM in response to jaxjason

Hello

I can only guess, for I don't use Numbers v3...

Does something like the following script force Numbers recalculate the formulae?

tell application "Numbers" to activate tell application "System Events" tell process "Numbers" keystroke "+-" using {command down} end tell end tell

It simulates pressing command + and command - in Numbers, that is changing font sise bigger and smaller which triggers recalculations with Numbers v2. You may select a range or table or any object with which text font size menu is enabled and run the script.

If this works with Numbers v3 as well, you may create an Automator service and assign a keyboard shortcut for it to be invoked.

Regards,

H

Reply