I want to ensure that the range c1:c200 has only numbers in it. If user enters anything else(like strings), we need to replace it with a zero. Also , once we enter numbers in 2 columns, how do we generate the sum of the values in each cell of the column, into the third column.
Use Data - Validation options. This is in Excel 2002, not sure which version of excel it was added.
In the 3rd column cell, define a formula to add the 1st and 2nd column cells. When the values in column 1 and 2 change, column 3 is automatically computed and updated.
Hi Guys,
Last post on todays shift :wink1:
Quote
Use Data - Validation options. This is in Excel 2002, not sure which version of excel it was added.
It was introduced with XL 97
Quote
I want to ensure that the range c1:c200 has only numbers in it
Following events-procedure should be placed in the actual worksheets modul (right click on the sheets tab and choose Show code and paste the following code)
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("C1:C200")
On Error Resume Next
Static rnOld
If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub
If Not IsNumeric(rnOld.Value) Then rnOld.Value = 0
Set rnOld = ActiveCell
End Sub
Quote
Also , once we enter numbers in 2 columns, how do we generate the sum of the values in each cell of the column, into the third column.
Let say we have column A, B and C.
Place following formula in C-column and copy it down in the column:
=IF(OR(A1="";B1="");"";SUM(A1:B1))
Don’t have an account yet? Register yourself now and be a part of our community!
