Using 2 team lists across 2 columns, add a score per each team depending on who answered

[domdc1]

Hi there.

I have probably made no sense at all ..... i will try to explain further

- i have 2 teams with different members (columns A & B)
- i have a list of questions (column c)
- In Column 'D' I want to add a data validation pull down list to select the names from team A or B (between a2:b8) - im not sure how to add a list across 2 columns, i only know how to add it in 1 column
- once the name is populated in column D, i need to have row 12 accumulate a score based on what team they are on
- team member on team#1 is selected in the pull down, team#1 total gets a point (row 13)
- as additional questions are answered, i need the totals to accumulate
today team #1 has a 5 & team 2 has 2 but next week team 2 can answer 2 additional questions, i want the total to change to 4 etc....

please help & as usual thanks so much !!!

 

[Dave87]

data validation from 2 sources either on a separate tab create 1 list and use this or else use a Macro [Source TechNet Data Validation list from two or more ranges]

ok to use a Macro

press Alt + F11 which will open up a new window, then in the toolbar click on Insert and then Module. Paste the code below

Option Explicit

Sub Validation_two_ranges()
    Dim a$, el As Range
    Dim rng1 As Range, rng2 As Range
        Set rng1 = Range("a2:a8") 'you can assign by namerange
        Set rng2 = Range("b2:b8")
    For Each el In rng1 'first range
        a = a & el.Value & ","
    Next
    For Each el In rng2 '2nd
        a = a & el.Value & ","
    Next
    With Range("d2:d8").Validation 'destination val.list
        .Delete
        .Add Type:=xlValidateList, Formula1:=a
    End With
        Set rng1 = Nothing
        Set rng2 = Nothing
End Sub

close the Microsoft VBA window and go back to your excel page, press Alt+F8 and click 'Run'. this should allow you to pick for multiple drop down lists.

counting the number of answers should be easy but i need to think about it

 

[domdc1]

Thats great !!! worked like a charm..... i made it work previously without a macro but since im old i forgot - lol. Any idea on the part 2?

 

[Dave87]

I'm still stumped on an easy way to do this, and i know there should be a straightforward formula using a countif and array but i just cant get it to work so long winded way of doing it

in cell a11 paste

=COUNTIF($D$2:$D$8,A2)+COUNTIF($D$2:$D$8,A3)+COUNTIF($D$2:$D$8,A4)+COUNTIF($D$2:$D$8,A5)+COUNTIF($D$2:$D$8,A6)+COUNTIF($D$2:$D$8,A7)+COUNTIF($D$2:$D$8,A8)

in cell B11 paste

=COUNTIF($D$2:$D$8,B2)+COUNTIF($D$2:$D$8,B3)+COUNTIF($D$2:$D$8,B4)+COUNTIF($D$2:$D$8,B5)+COUNTIF($D$2:$D$8,B6)+COUNTIF($D$2:$D$8,B7)+COUNTIF($D$2:$D$8,B8)