add formula to new row

[BondJames]

Hmm.. well i don't know will i explain good and will you be able to help.
Is there any way that every time i start write data in new row that formula automatically is placed there.

For example:
I have several columes:

Dayone Answer Calc

Dayone- is colume with dates
Answer is yes or no
And in calc should go with formula IF(Answer is No, then calculate today-Dayone if answer is yes that write blank)

Is it possible to done this or it's a wild goose chase..

 

[njimack]

=IF(B2="No",TODAY()-A2,"")

 

[BondJames]

tnx... but formula is not the problem.Problem is that i need that formula that it places it self every time when new row is written.
Lets say like this:

row1 contains already data and formula
when i select row2 and i write data in dayone and Answer that the formula is automatically placed there without have to pul from above..

Something like macro i need

 

[njimack]

Paste the following code into the module of the sheet in question

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Value = "No" Then Cells(Target.Row, 3).FormulaR1C1 = "=TODAY()-RC[-2]"
End Sub

 

[jasonb75]

Or just enable Formula Autocomplete (in excel options or press Alt Down)

Note that this doesn't work unless you have a consistant formula in the previous 4 (or more) consecutive rows of the same column.

 

[BondJames]

wow.. tnx that has done the job.. i have add some extra line

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Value = "No" Then Cells(Target.Row,_
 3).FormulaR1C1 = "=TODAY()-RC[-2]"
If Target.Column = 2 And Target.Value = "Yes" Then Cells(Target.Row, 3) = "0"
End Sub

 

[njimack]

One of the following would be more efficient:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
    If Target.Value = "No" Then
    Cells(Target.Row, 3).FormulaR1C1 = "=TODAY()-RC[-2]"
    Else: Cells(Target.Row, 3) = "0"
    End If
    End If
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Cells(Target.Row, 3).FormulaR1C1 = "=IF(RC[-1]=""No"",TODAY()-RC[-2],0)"
End Sub

 

[BondJames]

hmm.. i have made drop down menu (datavalidation) so when i switch from yes to no it leaves previous state.. any idea how it could refresh result??

Edit: MY bad it works
tnx again

 

[njimack]

Data Validation won't fire a change event, unless there's a cell linked to the cell containing the drop-down.

Is it really necessary to have a drop-down if the only options are Yes or No? You could still use data validation to ensure your users don't enter anything else into that cell.

 

[BondJames]

Data Validation won't fire a change event, unless there's a cell linked to the cell containing the drop-down.

Is it really necessary to have a drop-down if the only options are Yes or No? You could still use data validation to ensure your users don't enter anything else into that cell.

Yeah that is the point that users can't write "maybe" etc.. It works ok now