Editing a cell without deleting the formula

Short answer: No.

Numbers cells can contain a fixed value, entered directly from the keyboard or pasted into the cell, or they can contain a formula and display the value resulting from that formula. If you change the displayed value "3" (or FALSE, which will be the value returned by your formula for any value in A1 except 2) in your example by typing in a 4, what you type into the cell replaces the formula.

You can, however, edit the formula to make it show 4, rather than 3 for the stated condition. Or you could edit the formula to retrieve a value from another cell (rather than return the fixed value 3), in which case you could then edit the value in the referenced cell to change the value in the cell containing the formula.

Regards,

Barry

If you want the formula to set the value of B2, then the formula must be in cell B2.

Move the formula to B2 and edit it as shown:

=IF(A1=4,B2=5,"0") (will return TRUE if A1=4 AND B2=5, FALSE if A1=4 AND B2<>5, or "0" if A1<>4. Returned value will be placed in cell containing the formula (B1)

=IF(A1=4,5,0) (Returns numerical value 5 if A1 contains the numerical value 4, otherwise returns the numerical value zero.)

Numbers does not support macros. It is scriptable, using AppleScript, and scripts can set the value of a cell without being 'in' the cell. The value is editable, and fixed until the script is run again. The main Script guru in this forum is Yvan, so I'll defer to him on any questions concerning AppleScript.

Regards,

Barry

(1) Apple deliver a lot of infos but I can't read them for you.

(2) on my iDisk :

<http://public.me.com/koenigyvan>

you may find a lot of scripts dedicated to Pages or Numbers.

Yvan KOENIG (VALLAURIS, France) vendredi 3 juin 2011 10:41:23

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

theeditor66 wrote:

Is there any way to base a value in one cell off of another, and then manually edit that value without breaking the formula?

In other words...

i had a similar need recently and came up with the workaround(?) as seen in this file:

override_example.numbers

[i deleted/simplified most of that file in order to post here so some of it may not make sense (the popup for instance) + i simplified some of the conditions etc.. the gist should come through though]

basically, i needed to be able to calculate estimates based on market costs of items But, i also had to be able to manually enter actual quoted costs which would override the estimates without breaking their formulas in case i needed to revert to those numbers.

there's a note on the sheet which explains what's going on.

maybe you could apply something similar to the spreadsheet you're trying to create?

I built a slightly different scheme:

Inserting override value is not done on the main table.

It's done in the auxiliary table which I named "overWriters"

When a cell of column b is blank, the cell of the main table grabs the calculated value.

If the cell contain a value, this one is used.

I edited the conditional format accordingly.

At this time, the formula in D13 is :

=IF(ISBLANK(overWritters :: 1:1),D12,overWritters :: 2:2)

I'm not sure that my preferred variant will be accepted.

I would drop the use of D13 and would replace it by an edited formula in D12 :

=IF(ISBLANK(overWritters :: 1:1),SUM(B9:B11),overWritters :: 2:2)

and of course, the conditional format would apply to D12.

I leave the three other formulas as an exercise.

With the first scheme, in E36, the formula is :

=SUM(D34,D27,D20,D13,D6,D3)

With the alternate one it would be :

=SUM(D33,D26,D19,D12,D6,D3)

Yvan KOENIG (VALLAURIS, France) mercredi 22 juin 2011 18:01:12

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

hi Yvan,

thanks. i actually like that idea a bit better than my current implementation..

especially considering my estimating file actually has 8 different sheets (payroll,tracking,admin/profit,etc)..

one of the sheets contains multiple Vendor Material Lists which pulls items i have indicated on the material estimate and places those on tables to be sent out to various vendors.

I think it might make more sense to put something similar as your overwrite table on the vendor sheets which will then reflect on my cost estimating forms.

i'll have some time to try it out this weekend but i think it's going to stick.

thanks

Thanks.

Give us feedback when you will have edit your spreadsheet accordingly.

Of course, if the auxiliary table must not be seen by an other person, you may move it in an other sheet.

Yvan KOENIG (VALLAURIS, France) mercredi 22 juin 2011 18:18:31

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !