-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
when IF statement is not true....do not change cell
- Thread starter colmullet
- Start date
No Problem,
Its in a formula.
I have a sheet where i have row 1 (1,2,3,4...........)
I then have some analysis where the results change based on which column im looking at which i reference by the number in row 1.
Essentially i want to have an IF statement where IF(reference cell = row1, show result, otherwise do not change cell) so that i can change the reference cell to generate results for different columns and display the results at the bottom of each column......if that makes sense lol
If you need more clarification let me know.
Its in a formula.
I have a sheet where i have row 1 (1,2,3,4...........)
I then have some analysis where the results change based on which column im looking at which i reference by the number in row 1.
Essentially i want to have an IF statement where IF(reference cell = row1, show result, otherwise do not change cell) so that i can change the reference cell to generate results for different columns and display the results at the bottom of each column......if that makes sense lol
If you need more clarification let me know.
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
Excel formulas and functions (including IF) do not give instructions to Excel.
They return values.
In short, you can't do this with an Excel formula.
You can do this with a Worksheet_Change event. (VBA)
They return values.
In short, you can't do this with an Excel formula.
You can do this with a Worksheet_Change event. (VBA)
Upvote
0
I just tried something,
if i make the FALSE section of the IF statement equal to the cell im using the IF statement in, then it seems to work, although it does come up with the circular reference error....but is this not what i want? is there anyway to stop this error appearing everytime i change the reference parameter?
if i make the FALSE section of the IF statement equal to the cell im using the IF statement in, then it seems to work, although it does come up with the circular reference error....but is this not what i want? is there anyway to stop this error appearing everytime i change the reference parameter?
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
No. The circular reference error will come up constantly. Plus, because its an error condition, the result is unreliable.
Upvote
0
leighjboyd
New Member
- Joined
- Feb 1, 2016
- Messages
- 1
Hi, I had a similar problem where I wanted to keep a history of values from the past by using an If() statement.
I thought I could leave the last parameter blank
if(condition, value if true,)
but that didn't work. then I tried refering the cell to itself, which generated the circular reference warning.
But this caused erroneous behaviour. The next cells which used the result of this history, were giving strange resultes and I thought my spreadsheet was corrupt.
Apparently you need to tell Excel that circular references are okay.
Go File -> options -> Formulas
and check: enable iterative calculations
Then it stopped complaining about the circular references, and it gave more predictable results!
Hooray!
Upvote
0
