Preventing entry in more than one column

[halesowenmum]

Hi

I've got a table with several columns in which the user can enter a score of 2, 4, 8 or 16.

They only need to enter ONE score in one of these four columns. Obviously someone could accidentally fill in two columns and not realise which would then stuff up my lovely CF that categorises the total for me.

The cells involved for each score are as follows using the first row in which the scores need to be entered, which is row 3:

2	4	6	16
d3	f3	h3	j3

<tbody>
</tbody>

The rows involved are (inclusive) row 3 thru row 12.

Is there VBA I could use which, if the user filled in say column 3 and then lost track and filled in column h as well, I could have a message pop up saying that they've already entered a score and only one score can be entered per category?

Really appreciate your help on this very much.

 

[My Aswer Is This]

What columns are we dealing with?

How about user enters value in column A and column Z

 

[halesowenmum]

Hi

The columns are as shown in the table - d, f, h and j.

The user has to enter a numerical value in ONE of those columns and one only.

 

[Gerald Higgins]

You could use VBA, but there are lots of other ways.

For example, use a formula like this to make a warning message appear . . .
=if(counta(d3,f3,h3,j3)>1,"WARNING, more than one value entered","")

For example, use Conditional Formating to turn the row red if more than one value has been entered.
You could use a COUNTA formula as above.

 

[halesowenmum]

That's just the sort of thing that'll work for me; I think a warning message is just fine on its own however, I'm not clear on where exactly do I put this - in which cell/cells?

Thank you.

 

[Gerald Higgins]

You could put it, for example, in cell k3, and then copy all the way down so that there is a version of this formula for each line of data.

 

[halesowenmum]

Hmm, actually, that's not quite what I need.

The message appears in K3 as per your example but I'm sure someone would like completely miss it because it wasn't directly in front of their eyes !

Can it be that it displays the error message in col D when the duplicate entry is made in D and the message appears in D, (and etc the same for each of the other columns?). If it's relevant, the values that get entered in each column are specific: Col D = 2, Col F = 4, Col H = 8, Col J = 16.

 

[Gerald Higgins]

You can't put a solution like this actually in column D, if people are inputting data into column D as well, in the same cell.
I guess it could work if you put the message formula on the row above or below the input row, but I'm not sure if that would work for you.
You could also put it in, say, column E instead of D, but I'm guessing you have something else in column E already.

You could also use Data Validation to prevent users inputing more than one value.
Have you considered that ?

 

[My Aswer Is This]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D3:D12,F3:F12,H3:H12,J3:J12")) Is Nothing Then
Dim c As Range
Dim x As Long
x = 0
    For Each c In Range(Cells(Target.Row, 4), Cells(Target.Row, 10))
        If c.Value <> "" Then x = x + 1
        If x > 1 Then MsgBox "Only one entry allowed": Target.Value = "": Exit Sub
    Next
End If
End Sub

 

[My Aswer Is This]

Now that I test it more my script may cause a error if you have values in Columns E G or I

I will see if I can fix this.