IF Formula

Mahsun23

New Member
Joined
Oct 19, 2006
Messages
17
Hi there,

Is it possible to make an IF formula where the value of the cell is linked to another cell if TRUE, but the value doesn't change anymore if FALSE. In other words the cell should keep the latest value if FALSE.

Regards

Mahsun :cool:
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
Hi there,

Is it possible to make an IF formula where the value of the cell is linked to another cell if TRUE, but the value doesn't change anymore if FALSE. In other words the cell should keep the latest value if FALSE.

Regards

Mahsun :cool:
Mahsun23

Welcome to the Mr Excel board!

It sounds like you are trying to ask if a cell can contain both a formula and a value. If that is the case, then the answer is "no". However, a VBA solution may be possible.

Can you try to explain the problem again using specific cell references, specific values and specific formulas?
 

Mahsun23

New Member
Joined
Oct 19, 2006
Messages
17
Hi Peter,

thanks for replying

It is a simple formula: =IF(C8=U8,(C8/D8-1),?)

C8 en U8 are begin values. D8 is a current value. If the begin value changes, I would like this formula to stop working and keep the latest value.

maybe there is another solution?

regards
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
Mahsun23

Which cell is this formula in?
 

Mahsun23

New Member
Joined
Oct 19, 2006
Messages
17

ADVERTISEMENT

In cell W8
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
OK, so in W8 if C8 is not equal to U8 you want whatever was already in W8? If this is not correct, what do you want in W8 if C8 is not equal to U8?
 

Mahsun23

New Member
Joined
Oct 19, 2006
Messages
17

ADVERTISEMENT

"In W8 if C8 is not equal to U8 you want whatever was already in W8"

Exactly !!!

Do I want something impossible?

regards
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
"In W8 if C8 is not equal to U8 you want whatever was already in W8"

Exactly !!!

Do I want something impossible?

regards
This is not possible with a standard Excel formula. It is possible with VBA if you want to go down that road. I am about to sign off for now but I will have a look tomorrow to see if you are needing more help.
 

Mahsun23

New Member
Joined
Oct 19, 2006
Messages
17
You will break to new grounds if you can solve this problem. In my opinion it would be revolutionary. :biggrin:

I will be online tomorrow as well.

Regards

Mahsun
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
Mahsun23

I am not certain this will do exactly what you want but give it a try. First I suggest you make a backup of your file.

1. Right click the sheet tab and choose 'View Code'. This will display the VBA window.
2. Copy the code below and paste it in the large white pane at the right of the VBA window.
3. Close the VBA window, return to your sheet and try changing some values.

If it does not do what you expect, report back with as much detail as possible.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    Columns("IV").EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Range("C8").Value <> Range("U8").Value <SPAN style="color:#00007F">Then</SPAN>
        Range("W8").Value = Range("IV8").Value
    <SPAN style="color:#00007F">Else</SPAN>
        Range("W8").Formula = "=C8/D8 - 1"
        Range("IV8").Value = Range("W8").Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Forum statistics

Threads
1,136,589
Messages
5,676,679
Members
419,644
Latest member
KeelsM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top