Amending a cell value (XP/SP2)

Topic

New Reply

Is it possible to update the values of another cell
– what I have in mind is some “function” that on the basis of a test will increment the value of another cell
e.g. in pseudo-VB code
IF test = 1 then
target cell value = target cell value + 1
end if

I’m aware of the =IF(test,true condition,false condition) function, but if I use something like =IF(A4>10,K4=K4+1,K4=0) then the cell just display FALSE

Reply | Quote

Replies

Use a formula in K4 to update itself. If your K4 has a value, say 100, you could use

=IF(A4>10,100+1,100)

If K4 already contains a formula, say =B4+C4, you could use

=IF(A4>10,B4+C4+1,B4+C4)

Andrew C

Reply | Quote

A formula in a cell can not change another cell. There are a couple of possibilities. One would be to put the test into the target cell as Andrew suggested. A second might be to use the change event to trigger VBA code that would do what you want. However, we would have to know more details on what you want to accomplish. What is in the cell you want to add 1 to, a constant or a formula. What do you want to end up in the cell if it has a formula, the result of the formula with 1 added to it, or a new formula? What do you want to trigger the test to add one to the cell, a change in the cell being tested or something else?

Reply | Quote

Legare,
thanks for the feedback, and confirming my suspicions that a formula can’t change another cell
I’ll drop into VBA and hook into the Change event – gives me a reason to start looking at the Excel objects

Reply | Quote