Numbers - deleting data without deleting formulas

alysonfromon wrote:

How do I delete the information in the Numbers app without deleting all the formulae?

Suppose you have a table like this with formulae interspersed with data that you find inconvenient to separate ...

...And you want to quickly remove the data, leaving the formulae, so that it looks like this, ready for reuse with new data:

You can quickly accomplish that by running the script below.

1. Copy-paste the script from below into AppleScript Editor (in Applications > Utilities).
2. Select the cells you want to clean up, leaving the formulas. For example, here I selected B2:D8.
3. Click the triangle run button in Script Editor.

Some people are intimidated by scripts. But it is really easy to use one that is already written. Copy, paste, select cells, click to run.

Be sure to make a backup of your work before using this just in case you accidentally select the wrong cells.

SG

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

Hi SG,

Nice one!

Hi Alyson,

Just reading this discussion, may I add to previous replies. Maybe you are looking for a flexible formula that will update with just one edit in one cell.

Keep your data table intact and change the value (Year, in this example) in another table (Year total)

:

Formula in B2 of the Data table (and Fill Down)

=IF(A2="","",YEAR(A2))

If a cell in Column A is empty (""), then insert NULL ("") in the B cell, else extract the YEAR from the date in A.

Formula in B1 of the Year total table uses SUMIF

=SUMIF(Data::B,A1,Data::C)

Enter another year in B1 of the Year total table to sum the values for that year:

Please call back with questions.

Regards,

Ian.

Alyson,

Welcome to the forum for Numbers on mac os x .

The best way to make it easy to remove data without affecting formulas is to segregate them in separate tables.

In the example below, I placed data in a table named "Data" and the calculations are in a table called Statistics:

This way I can select any cell in the table Data, then select all, and delete to remove all data.

Hi alyson,

I do something similar to Wayne but I do have some formulas in my data table. I group the columns together. In the past I have saved templates for spreadsheets I renew every year. I tend to tweak my spreadsheets during the year and got tired of makking the changes twice.

quinn

It was already moved but how you find it, is click the iWorks icon (Numbers is an iWork app) and then click Numbers for Mac.

Would you like your post moved to the Numbers for Mac forum? I suspect you will find someone there that can help you.

Hi SG,

Always like seeing your scripts. Thinking about how formula might work for someone trying todo a global update to a formula in a table.

quinn