3

I use the Mac Numbers app (version 5.1) on a retina MacBook Pro, Safari 11.1.2

I need to copy & paste a spreadsheet to include the formulae, but not the figures?

Spefically, my accounts for July, with all the relevant formulae and I want to use the same layout - and, in general, the same formulae, but not the same figures - for my accounts for August. Up to now, I have copied & pasted the spreadsheet, then deleted the information in the relevant cells - but this also deletes the formulae.

  • Welcome to Ask Different. I've tried to edit this a bit - what precisely is a figure? Do you need steps to duplicate an entire sheet and then just clear the values from cells so that you have one sheet per month in the spreadsheet? – bmike Aug 26 '18 at 20:56
  • It sounds like a feature in Excel where you copy the cells you want and then just paste formulas (without the actual data) for what that info is worth... – Steve Chambers Aug 26 '18 at 21:08
  • @bmike - thank you for trying to help! By a 'figure', I mean a 'number' - e.g. number of miles travelled or amount of an expense. And yes, I need to duplicate an entire sheet and clear the values (numbers) from certain cells, keeping the formulae, so that I have one sheet per month. – Trish Aug 27 '18 at 9:37
  • It can not copy the formulas only, it will copy all, but you can delete the data afterward. – Ruskes Aug 27 '18 at 15:27
  • @Buscar웃 - but how to delete the data (e.g. the results of the formulae) without deleting the formulae themselves? The 2 answers given below are WELL out of my comfort zone...'scripts' and 'service'?! – Trish Sep 26 '18 at 9:54
1

The simplest way is to keep the data column separate from formula column, then just delete the data.

For more automated action I found this answer for you..

A short script can clear all the content in a selected range of cells except formulas, which are left intact. First use the File->Duplicate in Numbers to create a copy.

Next step... Copy-paste the script below into Script Editor (in Applications > Utilities folder). Select a range of cells in Numbers, some of which contain data while other have formulas. With the cells selected, click the triangle 'run' button in Script Editor.

It's always best to make a copy of your work before performing an operation like this, just in case you do the wrong thing or change your mind.

tell application "Numbers"
    tell front document to tell active sheet
        tell (first table whose selection range's class is range)
            set selRng to selection range
            repeat with aCell in selRng's cells
                tell aCell to if its formula is missing value then set its value to ""
            end repeat
        end tell
    end tell
end tell

data formula

  • Thank you for attempting to explain, but this is well out of my comfort zone! – Trish Sep 26 '18 at 9:55
0

One way to accomplish this would be to use automation. You can create a Service that deletes all non-formula cells in a selected table.

In your scenario, you'd copy and paste the entire old table—complete with the figures you want to delete—then execute the Service on the newly pasted table to remove all of the figures.

To do this:

  • Open Automator (/Applications/Utilities/Automator.app) and create a new Service.
  • At the top, set this service to receive no input in Numbers.
  • Use the search bar on the left side to find the Run AppleScript action and drag it into the main workflow pane on the right side.
  • Set the contents of the Run AppleScript to the following:

    on run {input, parameters}
        tell document 1 of application "Numbers"
            tell (table 1 of active sheet whose class of selection range is range)
                repeat with c in cells
                    tell c to if formula is missing value then set value to ""
                end repeat
            end tell
        end tell
    end run
    
  • Save the Service under a name you'll recognize (perhaps "Delete Figures").

From now on, whenever you are in Numbers and want to clear the raw figures from a table with formulas, select the table by clicking within it, then click Numbers in the menu bar, hover over Services in the menu that appears, and then click on the Service you just created (which I called "Delete Figures").

If you'd like to expedite this further, you could bind a keyboard shortcut to this Service in System Preferences:

  • Open System Preferences and open the Keyboard preferences.
  • Select the Shortcuts tab, then click App Shortcuts in the menu on the left.
  • Click the + icon in the lower left. Select Numbers as the application, and type the name of your Service exactly as it appears in the Services menu as the Menu Title. Then click on the Keyboard Shortcut field and press the keyboard shortcut you'd like to bind to the Service.

Now, when in Numbers, you can simply click within a table whose raw figures you'd like to delete, then press that keyboard shortcut to remove them.

  • Thank you for trying to explain this workaround - but I'm afraid it's still too advanced for me! I can't believe that Apple Numbers won't allow you to delete data whilst keeping the formulae! – Trish Sep 26 '18 at 9:59

You must log in to answer this question.

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