Automatic output based on another column, yet still allowing input if needed

[dforte]

Column A returns either "Yes" or "No". In column B I would like it to automatically return the number 0 if column A is "No", otherwise I would like it to allow me to enter in another whole number. Could someone assist with a macro for this?

[wmfinance]

I am assuming that when you say "another whole number," that it is somewhere already in your work book. Since I do not know where that information is, I have just left the False answer as your wording. Additionally, this equation only works if there is only "Yes" and "No" in column A:

=IF(A1="No",0,Another whole number)

To make the macro just click on developer-->record macro-->then stop it/save it after you have created the equation.

Hope this helps!

[dforte]

I see how the If formula works, but I want it to automatically put 0 if column A is "No" ... otherwise I want it to allow me to enter in any number I choose. Is there a way to allow the False portion of the formula to allow manual entry?

[ChemistB]

You could always overwrite the formulas if you don't protect them. I think you're looking for a spreadsheet event code.

This code will automatically put a 0 in column B if you type "no" in A. You can still write over it but that can be fixed with Data Validation if you want. This code only works as you are typing in the value (or select the cell and then click inside the formula box (or F2) then enter). Copy the code and right click on the spreadsheet tab >View Code, then paste.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
    If Target.Value = "No" Then
    
        Target.Offset(0, 1) = 0
        
    End If
End If
  
End Sub

Does that work for you?

[dforte]

This doesn't exactly work. I used column A and B as a reference for explaining, so I am not sure how to modify this code to fit my sheet. I guess a simpler form of what I would like to happen is that if the column with Y/N output is "No", I would like a "0" to appear in the dependent column. Otherwise it would remain blank allowing me to put in any whole number I choose.

[ChemistB]

Where it says "Target.column = 1 that assumes your Y/N are in Col A. change this to 2 for B, 3 for C, ....
Where it says "Target.Offset(0, 1) ", the 1 represents 1 column to the right. 2 would be 2 columns to the right -2 would be 2 columns to the left, etc.
So if your Y/n is in D, and you want your 0 in.....
A then Offset(0,-3)
E then Offset(0,1)
G then Offset(0,3)
Does that make sense?

[dforte]

It's about the learning... Thank you!