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.

 

[Jaafar Tribak]

How about using the Data Validation feature :

In cell D3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$D3 and copy down trough to cell D12
In cell F3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$F3 and copy down trough to cell F12
In cell H3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$H3 and copy down trough to cell H12
In cell J3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$J3 and copy down trough to cell J12

You can add a propper message to the user via the Data Validation Error Message Tab .

The above should also prevent entering non-numerical data as requested.

 

[halesowenmum]

Thanks, I really appreciate it.

 

[halesowenmum]

How about using the Data Validation feature :
In cell D3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$D3 and copy down trough to cell D12
In cell F3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$F3 and copy down trough to cell F12
In cell H3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$H3 and copy down trough to cell H12
In cell J3 Data Validation > Custom , put the following formula : =SUM($D3,$F3,$H3,$J3)=$J3 and copy down trough to cell J12
You can add a proper message to the user via the Data Validation Error Message Tab. The above should also prevent entering non-numerical data as requested.

Thanks Jaafar - I've tried this but I kind of end up needing two DVs (which I think you can only have one). I need to cover off two things - stopping people populating more than one column and restricting them to entering the correct numbers in each column. I think I'll probably need to end up using VBA for the first bit and DV for the second. The VBA will (I hope! ) flash up a message only when the person enters something into more than one column, and in the background I'll have DV to ensure that only the correct numbers can be accepted in the right columns.

But, thank you for this as I've not really used the DV formulas bit before so it means I'll probably explore a bit more on this for my own learning and have it as a possible solution for other things and that's very valuable in itself so thank you for your reply.

 

[Gerald Higgins]

If you're going down the Data Validation route, Jaafar Tribak's solution will probably work in the circumstances you have described, but there are some slightly different circumstances where it will not work, so I would use a different formula in the Data Validation -

=COUNTA(D3,F3,H3,J3)<2

Adapt this for the other columns.

Reason for using this version - IF your users input non-numeric values, my version will stop multiple entries, Jaafar's won't.
I appreciated that you might control this through the drop down menu anyway.