Excelled, I had to read this a couple times but I think I disagree, not with your solution but with your reading of agent007's goal.

Agent007, excelled is right that the IF function requires a value both for the True and the False condition. The first couple times I read your description, I wanted to ask "What's in D1 in the first place?" Say D1 is normally =A5*PI(). Then, if you want it to be A5*PI() normally but to have C1's value whenever B1=1, then you should put this in D1:But after another reread, I decided you're saying this: You want the value in D1 to be whatever it is — whatever it was before — but when B1 changes to 1 you want D1 to change to the value of C1 and stay that way even if B1 changes again.

If that's what you want, then I don't think you can get there with formulae. You need a VBA macro. Are you up for doing that?