Preventing manual data entry errors

[willtmc]

I can't figure out how to protect my spreadsheet from data entry errors.

I have a column of data that must be entered manually (column B). Sometimes, the entry in column B could have been calculated by running a formula run upon the data in column A. Sometimes it has to be entered manually.

Is there a way to use data validation or conditional formatting to make sure that the proper figure is entered into column B when it could have been calculated by a formula to avoid data errors?

Thanks,

Will

 

[jim may]

Providing a sample of your raw data and the results you are looking for is always MORE helpful to us

 

[willtmc]

In the example below, the contents of column F must be entered manually. In all cases where column C = Dividend, however, the results in column F should be 0. How do I make sure this happens every time?

Annualized rate of return.xls
	C	D	E	F	G
1	Transaction	Symbol	Name	Quantity	Price
2	Purchase	USAXX	MoneyMkt	250.000	$1.00
3	Dividend	USAXX	MoneyMkt	0.000	$0.00
4	Purchase	USAXX	MoneyMkt	0.020	$1.00
5	Dividend	USAXX	MoneyMkt	0.000	$0.00
6	Purchase	USAXX	MoneyMkt	0.310	$1.00
7	Purchase	USAXX	MoneyMkt	200.000	$1.00
8	Dividend	USAXX	MoneyMkt	0.000	$0.00
9	Purchase	USAXX	MoneyMkt	0.300	$1.00
Raw Data

 

[Peter_SSs]

willtmc

Welcome to the MrExcel board!

See if this does what you want (assumes you are not using Ecel 2007):

1. Select F2:Fxx (end of col F data)

2. Data|Validation...|Settings tab|Allow:Custom|Formula: =IF(C2="Dividend",F2=0,F2>0)|OK

 

[stanleydgromjr]

willtmc,

You can use the "Worksheet_Change" Event.

You can adjust the Range("F2:F1000") to fit your data set in worksheet "Raw Data", from 1000 to the maximum row you will be using.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Right click the sheet tab you want the code in ("Raw Data"), and click on View Code. Paste the below code there (on the right pane).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("F2:F1000")) Is Nothing Then Exit Sub
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  If Target.Offset(, -3).Value = "Dividend" Then Target.Value = 0
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Then make changes to entries in column F.


Have a great day,
Stan

 

[willtmc]

Both of the solutions above work great. Thanks a bunch for your help.

I do have one further question, however. Is it possible to alter the code above to automatically fill the cell in column F with the value 0 when "Dividend" is selected in column C? I have a feeling this must be possible and would make life slightly easier than the data validation solution.

Thanks,

Will

 

[stanleydgromjr]

willtmc,

Sorry - my mistake.


Have a great day,
Stan

 

[stanleydgromjr]

willtmc,

You can adjust the Range("C2:C1000"), and the Range("F2:F1000"), to fit your data set in worksheet "Raw Data", from 1000 to the maximum row you will be using.

Replace the original code with the below code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C2:C1000,F2:F1000")) Is Nothing Then Exit Sub
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  If Target.Column = 3 Then
    If Target.Value = "Dividend" Then
      Range("F" & Target.Row).Value = 0
    End If
  ElseIf Target.Column = 6 Then
    If Target.Offset(, -3).Value = "Dividend" Then
      Target.Value = 0
    End If
  End If
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

 

[willtmc]

Stan,

Thanks for the help with this request. I modified the code you sent to make sure that column G was also filled with "0" if "Dividend" was selected. I did it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C2:C1000,F2:F1000")) Is Nothing Then Exit Sub
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  If Target.Column = 3 Then
    If Target.Value = "Dividend" Then
      Range("F" & Target.Row).Value = 0
      Range("G" & Target.Row).Value = 0
    End If
  ElseIf Target.Column = 6 Then
    If Target.Offset(, -3).Value = "Dividend" Then
      Target.Value = 0
    End If
  End If
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

I was able to understand the code well enough to copy that line to duplicate the result. I don't understand what code after the line I duplicated does. I hope I didn't shoot myself in the foot by making this modification. It seems to work just fine.

Please let me know if I should have modified the code in a different manner.

Will

 

[stanleydgromjr]

willtmc,

Please let me know if I should have modified the code in a different manner.

If you modified the code, and it does what you want, then you are all set.


Have a great day,
Stan