Column A, each cell has value of 1
... is
there a way to put a check box or button in each cell that allows the
user to select ONLY ONE of the three columns at a time. And when the
selection is made I want a specific value to be assigned to that cell
(as indicated below). Then I need to add the total 'value' for each
column.
The following doesn't work exactly how you describe, but it's close and it
uses basic Excel features.
First, enter these text values:
In E1: A
In E2: B
In E3: C
Column D will have a pull-down in each cell to choose A, B, or C. To do
this, select column D and use
Data > Validation >
Allow: List
Source: =$E$1:$E$3
Check "Ignore blank"
Check "In-cell dropdown"
Style: "Stop"
Check "Show error alert ..."
Select column D and use:
Format > Cells > Border
to put borders around the cells to suggest that's where the user makes the
input choices.
Enter these formulas:
In F1: =SUM(A:A)
In F2: =SUM(B:B)
In F3: =SUM(C:C)
In A1: =IF(D1="A",1,"")
In B1: =IF(D1="B",2,"")
In C1: =IF(D1="C",4,"")
Select A1:C1 and copy down for as many rows as needed.
The totals for the columns are in E1:F3.
Error checks can be added (in column G, say) to detect if the user
erroneously types into columns A:C. Better yet, read up on "protection" in
Excel's built-in Help.
Modify to suit.