Solved Counting occurrences from a drop down list

April 12, 2016 at 06:36:59
Specs: Windows 7
How do you create a drop down menu and count the number of times you click that value into another cell? But have the drop down menus disappear once you've chosen your value so that you can choose another one but still count your previous selection?

Here's the scenario:
I have about 20 values in a drop down list according to each person, but a person can have more than one of those categories over a month's span.

I have those same values spread across right next to it, 20 columns.
Each of those has a box to tally each category when chosen.

I want to pick a value from the list, have it recorded, then be able to choose another one but keeping the past selections. Hope I explained this well

message edited by mike.vandam


See More: Counting occurrences from a drop down list

Report •


#1
April 12, 2016 at 07:15:09
Well, I'm confused. Keep in mind that we can not see your worksheet from where we are sitting.

re: "I have about 20 values in a drop down list according to each person, but a person can have more than one of those categories over a month's span."

What do you mean by "according to each person"? Do you mean that you have a column of names and a drop-down list next to each name?

re: "I have those same values spread across right next to it, 20 columns. "

By "it" I assume you mean the drop down list, correct?

re: "Each of those has a box to tally each category when chosen."

I have no idea what that means.

re: "I want to pick a value from the list, have it recorded, then be able to choose another one but keeping the past selections. "

Have it recorded where? Keep the past selection where?

I don't see the relationship between your "20 columns of values", the drop-downs, the tally, etc.

Perhaps some example data would help. Please click on the following line and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 12, 2016 at 07:26:15
Sorry, I see now that I was VERY confusing!

Agent Name	Sub-reason		ACH	AFF	AIC	ALD	BPA	BUY	CA	CC	CO	COMM	DM	DUP	ID	IDL	IN	INM	IWO	MOD	MQP	NA	NAT	NIC	NLP	NLS	NO	PDP	PF	RO	TR	WSC	YZ	Total
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
name																																		0
Total:			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
																																		
																																

Under sub-reason, there are aprox 31 choices to choose from a drop down list.

Next to that are the 31 choices, each having a box to indicate your selection
from the drop down menu (sub-reason).

I want to choose my selection under sub-reason (drop down list), have it
recorded when I choose the reason (I was using Countif).

Now, I need to make more than one selection under each person.

With a single drop down list, how can I record multiple selections while
keeping the past selections?

edited by moderator: Formatted for easier reading


Report •

#3
April 12, 2016 at 12:02:08
✔ Best Answer
The only way I can think of accomplishing your goal is with a Worksheet_Change macro. Maybe someone else will come up with a formula based solution.

Since you didn't include any Column letters or Row numbers, I'm assuming your example data starts in A1, with drop-downs in Column B and "ACH, AFF", etc. in C1:AG1.

The following code will search C1:AG1 for the latest drop down choice and place an X in the corresponding Column/Row.

Modify as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Column B
  If Target.Column = 2 Then
'If change was made to Column B, matching Heading in Row 1
     With Range("C1:AG1")
      Set d = .Find(Range("B" & Target.Row), lookat:=xlWhole)
'If Heading is found, place X in corresponding Column/Row
        If Not d Is Nothing Then
           Cells(Target.Row, d.Column) = "X"
        End If
    End With
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question