1

Lets say I have a formula like (Q7+Q11)÷Q20 in cell O20 and I wanna copy it to another cell, lets say D47, is there any key modifier or shortcut to do it without losing the original references of the formula and without rewriting it?

Something like Cmd+Alt drag and drop?

I am with Numbers 4 on Mac OS Sierra

  • 1
    Many thanks for taking so much time to elaborate these answers @Synoli, I was aware of some of the details but I was expecting something more direct from Numbers, I guess it simply doesn't exist – Jaime Agudo Mar 12 '17 at 14:56
  • Once the AppleScript is installed, you simply select your cells while in Numbers and press ⌥⇧⌘C as mentioned. It will also appear as a menu item in Numbers » Services if using the mouse is more your thing. How more »direct« would you want it to be? – Synoli Mar 12 '17 at 16:44
2

Using the built-in method

As per Apple’s Knowledge Base, Numbers supports absolute references:

You can set row and column references in a formula to be absolute so that you can use the same formula elsewhere in your spreadsheet without the cell references changing, as they would otherwise.

To change your original formula to use absolute references, follow these steps:

  1. Double-click on the original cell to go into edit mode.
  2. For each reference, click on the triangle; then tick both boxes Preserve Row and Preserve Column.

Or, as an alternative to step 2, you can manually edit the reference to say e. g. $A$1 instead of A1.

The obvious drawbacks of this method are:

  • The method basically locks your formula in a mode that makes it hard to reuse the same formula in other places where you actually want flexible references.

  • If you you want to copy many different original formulas, you need to repeat the method over and over.

1

Using an AppleScript to Copy Formulas

A more reusable approach would be a simple AppleScript that replicates the Copy command but only copies the bare formulas:

on join(cells, delimiter)
  set previousDelimiters to AppleScript's text item delimiters
  set AppleScript's text item delimiters to delimiter
  set resultString to (cells as text)
  set AppleScript's text item delimiters to previousDelimiters
  return resultString
end join

on getSelectedFormulas()
  set tab to character id 9
  set lf to character id 10

  tell application "Numbers"
    tell document 1's sheet 1's table 1
      tell selection range
        set {min, max} to {column 1's address, column -1's address}
        set formulaRows to {}
        repeat with aRow in rows
          set formulaCells to {}
          repeat with aCell in aRow's cells min thru max
            set formulaCells to formulaCells & (aCell's formula)
          end repeat
          set formulaRows to formulaRows & ¬
            my join(formulaCells, tab)
        end repeat
        return my join(formulaRows, lf)
      end tell
    end tell
  end tell
end getSelectedFormulas

on run()
  tell application "Numbers"
    set the clipboard to my getSelectedFormulas()
  end tell
end run

(Note: I’m the author of the script; it’s under the ISC open source license.)

This also eliminates the need to modify the original formulas.

Binding the script to a keyboard shortcut

In order to bind this script to a keyboard shortcut in Numbers, open Automator.app to create an Automator workflow. Choose the option Service receives no input in Numbers, create a single Run AppleScript step and paste the AppleScript code there.

Save the workflow under the name Copy Formulas, then open System Preferences and click on Keyboard, then Keyboard Shortcuts » Services » Numbers » Copy Formulas. Configure a keyboard shortcut; I’d recommend ⌥⇧⌘C because you’re probably going to use it often with its counterpart, Paste and Match Style, which already has the shortcut ⌥⇧⌘V.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .