trying to do an -- if then else statemnet with different cells

[hydro]

I have data in one cell that I want to repeat based on the input to another cell otherwise I want to enter different data. for example -- a name in j117 that I may want to repeat in d140 or I may want to enter different data in d140 depending in the 'yes' or 'no' input in cell a140.
If a140 = yes then the cell d140 = the value in j117
if a140 = no then the cell d140 is blank and I can enter different data without overriding the formula
where is the formula to be placed? in d140 or in a different cell? I tried putting an 'if' formula in B140 so I could still enter data in d140 but that did not work
any ideas?

[Olly]

For the output of the IF statement to affect D140, then the IF statement formula must be in D140. If you overtype data into D140, the formula is then gone.

So, one potential solution:
Enter your 'alternative text' in B140
In D140 enter:

Please Login or Register  to view this content.

[ChemistB]

1) Formulas can only affect the cell that they are located in.
2) You cannot have both a formula and allow manual data entry into the same cell.

The only way to do this is with VBA. Are you open to a VBA (coding) solution? It would require having "macros" enabled for that workbook.

[hydro]

I have never done VBA but am willing to give it a try.