Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
an IF function that can take no action?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
an IF function that can take no action?
to me you have to set refeerence to othe cell than A1
otherwise you'll get a circular reference error |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
an IF function that can take no action?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
an IF function that can take no action?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
an IF function that can take no action?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow no action to be taken | Excel Worksheet Functions | |||
Repeating Last Action | Excel Discussion (Misc queries) | |||
OLE Action Issues | Excel Discussion (Misc queries) | |||
How to insert Open File action in the IF Function? | Excel Discussion (Misc queries) | |||
what is OLE.action | Excel Discussion (Misc queries) |