Numbers - Remove numbers but leave formulas

colorogall wrote:

how can I remove one month's numbers....but leave the formulas

You can use a short script to reset data cells but leave formulas intact. No AppleScript knowledge needed. Just copy, paste, select, click.

Step 1. Copy-paste script below into Script Editor (in Applications > Utilities.

Step 2. Select the range of cells with data and formulas.

Step 3. With the cells still selected, click the "run" button in Script Editor

Step 4. No step 4.

("If nothing happens" make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.)

The script:

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

If you are going to do this often then you might consider putting the script in the Script Menu for easy access.

SG

Start by determining which cells contain entered numbers and which contain formulas.

Then determine which of the entered values are 'permanent' and which can be deleted.

Current totals are dependent on these numbers, and will disappear when you detelte the numbers on which these totals are based. To preserver these formula generated numbers you will need, before deleting any values, to Copy those cells, select the corresponding 'beginning value' cell, and use the Paste Formula Results menu item in the edit menu to paste these as 'fixed' values into the cell designated to hold that value, replacing the 'beginning value' for the previous month.

Once you have all 'month start' values entered in tis manner, you can go ahead and delete the day-yo-day entries to ready the table for 'this month's' data.

You need clarification, please include a screen shot of your table showing the column and row reference tabs. You might want to make a copy of the table with personal information deleted or otherwise hidden. The purpose of the image i to show the structure of your table, not your personal finances.

Regards,

Barry

SG makes two good points here:

Using a script simplifies the process and makes it repeatable without the manual checking to determine which cells' contents are to be deleted and which are to be left as is.

Placing the script in the Scripts menu makes it more accessible.

An alternate to using a script is to use a template.

Clear the table once using either of the methods suggested above.

When the table is ready, and before entering new data, SAVE the document as a Template.

Then, each time you need a new copy, go to the File menu, choose New from Template Chooser*, and choose your new template.

This also gives you the option of archiving the previous months' tables or discarding them as you see fit.

Regards,

Barry

*If New from template chooser is not the first item on the File menu, press and hold the option key to make it appear, replacing "New".

I think you'll find using the script I posted above is quick and convenient.

No messing around with remembering each time to save your document as a template, then creating a new document from the template.

Just select cells and run.

SG

SGIII wries:

No messing around with remembering each time to save your document as a template, then creating a new document from the template."

There is no need to "(remember) each time to save your document as a template." The template, like the scipt, needs to be created only once. Once the template has been created it can be used once a month to create the new document for the new month.

The amount of 'messing around with 'remembering each time to use the template to create your new document' is about the same as the amount of messing around with "remembering each time" to select the existing table, then go to the Script menu and choose the script to run it."

Regards,

Barry

Barry makes one good point here. You can mess around with templates. That will work to a point, if your financial spreadsheet does not have accumulating prior periods or other changes that need to be retained.

With your document open go in the menu to File > Save As Template ...

Then to use the template in the future you can:

1. Go to File > New
2. Scroll all the way down to the bottom to the My Templates section
3. Locate the template and double-click it
4. Save the new document under a new name.

Contrast that with using the script posted above:

1. Select cells
2. Choose 'Reset data cells but keep formulas' (or whatever you have named the script) from the Script Menu.
3. Let the document keep autosaving under its original name

Note that a template will have no memory of prior periods or any other changes you may have made to your document. If you want to preserve those then you will have to create a new template each time. With a script you don't need to worry about any of that.

Removing data while leaving formulas intact is a common need in financial spreadsheets. Another common need is "removing" formulas to "fix" values, often in prior periods. To do that simply select a range of cells, command-c to copy, and in the menu Edit > Paste Formula Results. This removes formulas without affecting selected cells that do not have formulas in them.

SG