[shoy]

Does anyone know how to make an IF function take no action if the logic test
is false?. I want to change the value in a cell if the logic test is true,
but leave the current value alone if false.

It seems like MS forces the cell to be zero or blank or whatever the third
part of the argument is set at if the logic test is false

I remember using =IF(B5=1,10,A1) in cell A1 in an older spreadsheet
program, which set cell A1 to 10 if B5 was 1, but otherwise left the cell's
current value alone. It definitely worked. However MS finds the reference to
A1 within cell A1 a circular reference.

thanks
--
shoy

[Jarek Kujawa[_2_]]

to me you have to set refeerence to othe cell than A1
otherwise you'll get a circular reference error

[dennis]

This makes no sense. Cell A1 is either a value or the result of a formula.
If its a value then use =IF(B5=1,10,Currentvalue)
If its a formula then use =IF(B5=1,10,CurrentFormula)

"shoy" wrote:

Does anyone know how to make an IF function take no action if the logic test
is false?. I want to change the value in a cell if the logic test is true,
but leave the current value alone if false.

It seems like MS forces the cell to be zero or blank or whatever the third
part of the argument is set at if the logic test is false

I remember using =IF(B5=1,10,A1) in cell A1 in an older spreadsheet
program, which set cell A1 to 10 if B5 was 1, but otherwise left the cell's
current value alone. It definitely worked. However MS finds the reference to
A1 within cell A1 a circular reference.

thanks
--
shoy

[Mike H]

Hi,

How is A1 populated with this value you want to retain? If it's from another
cell then reference that cell or if it's a fixed vlue then use that fixed
value in the formula.

=IF(B5=1,10,100)

Mike

"shoy" wrote:

Does anyone know how to make an IF function take no action if the logic test
is false?. I want to change the value in a cell if the logic test is true,
but leave the current value alone if false.

It seems like MS forces the cell to be zero or blank or whatever the third
part of the argument is set at if the logic test is false

I remember using =IF(B5=1,10,A1) in cell A1 in an older spreadsheet
program, which set cell A1 to 10 if B5 was 1, but otherwise left the cell's
current value alone. It definitely worked. However MS finds the reference to
A1 within cell A1 a circular reference.

thanks
--
shoy

[shoy]

Thanks Dennis and others,

There is a reason behind the madness of it.

I use it to populate a number of tables, each representing the values from a
single scenario, but where I change the variables within the scenario to get
multiple results.

So in the tables and against for example cell A1, I use
IF($B$5=1,scenarioresult,A1). This means that I set up a scenario with
certain variables, then by controlling B5 to be 1 (or whatever), I can switch
on or off cells to pick up the scenario results or ignore them.

I then generate a number of scenarios in turn, each time populating my
chosen results tables without affecting previous results tables.

I cant think how else to do it without doing programming which I am no good
at. Interestingly, the whole thing works, although MS complains about
circular references when the spreadsheet is first opened, but I OK to this
and then it stops complaining. I would like a better way though. I thought
your "currentvalue" and "currentformula" were the functions I was looking for
- but I dont think they are functions are they? You were just writing them to
explain your point.

shoy


"Dennis" wrote:

This makes no sense. Cell A1 is either a value or the result of a formula.
If its a value then use =IF(B5=1,10,Currentvalue)
If its a formula then use =IF(B5=1,10,CurrentFormula)

"shoy" wrote:

Does anyone know how to make an IF function take no action if the logic test
is false?. I want to change the value in a cell if the logic test is true,
but leave the current value alone if false.

It seems like MS forces the cell to be zero or blank or whatever the third
part of the argument is set at if the logic test is false

I remember using =IF(B5=1,10,A1) in cell A1 in an older spreadsheet
program, which set cell A1 to 10 if B5 was 1, but otherwise left the cell's
current value alone. It definitely worked. However MS finds the reference to
A1 within cell A1 a circular reference.

thanks
--
shoy