Vote sheet

[Janning197]

Hi,
I have an easy one (not for me). I basically want to make a vote sheet, columns Name (optional), For, Against. (columns A,B,C which can have ..oh? say 1000+ rows) Column D will be empty.
(Frist row, will be header/column names)
Column E will be one cell E2, total FOR's - counts the total number of "X" in Column B rows ,
Column F will be one cell F2, total Against's - counts the total number of "X" in Column C rows ,
and Column G - one row - total votes...is total of E2 + F2
Columns, E,F, and G are protected (I think I got that part)

how can I?
1) Force rows in column B contain "X" or null, (and if the same row has data in column C has an "X", that B row has to be null)
2) Force rows in column C contain "X" or null, (and if the same row has data in column B has an "X", that C row has to be null)
3) Cell E2 counts all the "X's" in Column B
4) Cell F2 counts all the "X's" in Column C
5) Cell G2 is simply E2+F2 (I have this one working)

Below is what I've thrown together so far...the 1's should be X's

http://www.aanning.com/ajissues/VoteSheet/vote1.jpg

Thanks James

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. make your entries in columns B and C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B,C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.Column = 2 And Target.Offset(0, 1) <> "" Then
        Target.Offset(0, 1).ClearContents
        Cells(2, "E") = Cells(2, "E") + 1
        Cells(2, "F") = Cells(2, "F") - 1
        If Cells(2, "E") = -1 Then
            Cells(2, "F") = 0
        End If
    ElseIf Target.Column = 2 And Target.Offset(0, 1) = "" Then
        Cells(2, "E") = Cells(2, "E") + 1
    End If
    Cells(2, "G") = Cells(2, "E") + Cells(2, "F")
    If Target.Column = 3 And Target.Offset(0, -1) <> "" Then
        Target.Offset(0, -1).ClearContents
        Cells(2, "F") = Cells(2, "F") + 1
        Cells(2, "E") = Cells(2, "E") - 1
        If Cells(2, "E") = -1 Then
            Cells(2, "E") = 0
        End If
    ElseIf Target.Column = 3 And Target.Offset(0, -1) = "" Then
        Cells(2, "F") = Cells(2, "F") + 1
    End If
    Cells(2, "G") = Cells(2, "E") + Cells(2, "F")
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Janning197]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click '

Sorry, I forgot an important detail....I want to have this working in Google Drive (sheets) will macros work there ?

 

[mumps]

To be honest, I have no experience using Google Drive. You'll have to try it to see if it works.

 

[Janning197]

To be honest, I have no experience using Google Drive. You'll have to try it to see if it works.

It does not. I'm going to have to use descreet formulas. I'm almost there. What can I used to count any value in a column? not just X..they may fat finger a "s"

 

[Janning197]

Ok, got that..usned COUNTA function. Here is where I'm at, in the below, I have # 3,4 and 5 working
I don't even need it to be "X"...any value will work...great. what I do need is, if, for a row, if B has value, C can not and vice versa


1) Force rows in column B contain "X" or null, (and if the same row has data in column C has an "X", that B row has to be null)
2) Force rows in column C contain "X" or null, (and if the same row has data in column B has an "X", that C row has to be null)
3) Cell E2 counts all the "X's" in Column B
4) Cell F2 counts all the "X's" in Column C
5) Cell G2 is simply E2+F2 (I have this one working)

 

[mumps]

I'm not sure how you could do this with formulas.

 

[Janning197]

I'm not an excel expert, but have used lots of other tools....If I throw out some ideas it may trigger you. I'll stick to specific needs, my Column, B and C
Is there any function which, If I type any value in B2, will disable C2? (Cell level only, next row may need C3)
Is there any function which, If I type any value in B2, will Null C2?
Is there any function which , if there exists a value in C2, warns you to not enter values in B2?

 

[mikerickson]

Parts 3 and 4 can be done with COUNTIF function

=COUNTIF(B:B,"X") will return the number of cells in column B that have an X.

For 1) and 2) you'd need to use Validation with a formula like =OR(AND(B1="X",C1=""),AND(B1="",C1="x"),AND(B1="",C1=""))
The Reject bad entry option and the Help message features would also be used

 

[Janning197]

Parts 3 and 4 can be done with COUNTIF function

=COUNTIF(B:B,"X") will return the number of cells in column B that have an X.

For 1) and 2) you'd need to use Validation with a formula like =OR(AND(B1="X",C1=""),AND(B1="",C1="x"),AND(B1="",C1=""))
The Reject bad entry option and the Help message features would also be used

AH! yes I can make that work =OR(AND(B1="X",C1=""),AND(B1="",C1="x"),AND(B1="",C1=""))
But now I'm not going with "X"..its any value, so I need something like an " is not null"
=OR(AND(B1 is not null ",C1=""),AND(B1="",C1 is not null), AND(B1="",C1=""))

What is the syntax for that?