Can Excel recognize when data is entered and apply formulas?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can Excel recognize when data is entered into a cell and then apply the
appropriate formulas from the cells above? I have a spreadsheet set up for
people to use but some of the cells are locked and I don't want to print the
whole worksheet until info is entered into cells and then the formulas be
applied.
 
You could use an worksheet event. The example code below will copy the formula in Column C from the
row above the entry in Column B.

For example, if you have a value in cell B2 and a formula in cell C2, and row 3 is blank, then when
you enter a value into cell B3, the formula from C2 will be copied into cell C3, and so on.

Copy the code, right-click on the sheet tab, select "view code" and paste the code into the window
that appears.

If you need help customizing this to your particular situation, post back.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Target(1, 2).HasFormula Then Exit Sub
Application.EnableEvents = False
Target(1, 2).FormulaR1C1 = Target(0, 2).FormulaR1C1
Application.EnableEvents = True
End Sub
 
Back
Top