Zipporah has attended:
Excel Intermediate course
Excel Advanced course
Project Introduction course
Project Intermediate course
Lists and Functions
How do I count the number of times a perticular name was ticked in a multiple selection drop down list?
RE: Lists and Functions
Hi Zipporah
Thanks for getting in touch. Could you clarify the context for me please? How is the multiple selection drop down list created?
I can think of a couple of ways to create such a list so I want to make sure I'm giving you accurate advice for that particular feature.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lists and Functions
Hi,
I created a multiple selection drop down list using Developer Ribbon,Design Mode,Insert List box under ActiveX controls.Then I selected the list I wanted as a drop down.
I created this for a number of rows.
But now I need to count how many times a persons name is selected in the list.(I have to consider all the rows I created the list for as they all use the same list)
I have to then show the count next to the persons name on a seperate spreadsheet which has the list.
Regards,
Zipporah
RE: Lists and Functions
Hi Zipporah
Thanks for your reply. You can derive this number, but you need some understanding of Excel VBA programming in order to achieve your goal. The code would be something like this:
For each item in listbox
if item.value = person_name and item.value Is Selected = true
counter = counter + 1
end if
next item
You would then need to report back the value of counter. This code will vary wildly according to how the sheet is setup.
I can see that you've attended the Excel Intermediate and Advanced courses, and we offer courses on VBA programming that can help you work with this code.
If VBA programming is not an option for you, and if there is any possibility you can *not* use the ActiveX ListBox you will find easier ways to do this. For example, if the person names are listed in separate rows, you can use a function such as COUNTIF to determine how many times a name occurs.
Consider weighing up the possible options; if you think the best way is to use the ListBox it's probably easier for us to see a copy of the file to work out a solution. Let me know if that's the way you want to go and we'll take it from there.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lists and Functions
Hi,
I can send you a mock project file, but how do I do that?
I will stick to the list as I don't know VBA.
I need to calculate the number of ticks for each project and display that number. Then using that I need to compare it with the database to see if the required numbers are met and show deficits by colour(Red-lacking or over allocation or blue-ok).I also need the ticked names to display (like a note)When I hover over/click the dashboard cells in question.
Hope this makes sense. If not I can explain in more detail.
Regards,
Zipporah
RE: Lists and Functions
Hi Zipporah
It's probably easiest to mail the file directly to me:
gary@stl-training.co.uk
I suspect a ListBox from that set of controls will not be the easiest way to go. But let's have a look.
If you can leave a phone number with your email I'll call if I need to discuss the solution.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lists and Functions
Dear Zipporah
Thanks for the file.
This forum is mainly designed to handle specific questions and issues, but it is not uncommon to get requests similar to yours.
Looking at the workbook, this will be best suited to a VBA solution and will take some time to write. This type of work falls under consultancy as it is outside the scope of this forum, and any agreed solutions will be billable.
In the meantime, you may want to search for "using listbox excel vba" to give you a hint in the right direction.
I hope this helps, please get in touch if you'd like us to investigate the issue further.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
