Selectable List Box Form Control (Not ActiveX) question on possible use

Hi Guys/Gals,

Quick question before I spin my wheels unnecessarily.

I have a dashboard worksheet amongst other worksheets such as raw data, calculation, and a list worksheet. What I have on my dashboard worksheet is a list box control form (non active x) that has single-selectable years. When I select 2010 or any year, my charts update fine because my list box control form has input ranges and cell links that are sourced from the cells calculation worksheet. Not a problem here.

I also have another list box control form (not active x) that has input range and cell link to show a long selectable list of people's names. Straight forward control. When I click a name, another section on my dashboard provides vital statistics about those people. However, this list box is not driven by Year. It is all people from 2009 - current.

What I want to do and not sure if possible without using VB (don't know much), is when a year is selected to only display the people's names based on the year that was selected.

Is it possible to use List Box Control Forms (without Active X) to dynamically display selectable data (people's names based on year criteria) in a new List Box Control Form (without active X), where I can then continue to use input ranges and cell link? I cannot see how as there is no way to put a formula in to say "IF =YEAR Then display all people's names".

Hope I explained well enough.

Thanks,

Tom

Thank you,

Tom

Hello
If you're prepared to create a few ranges with references it might be possible. Take a look at the attachment for an idea.

Hope this is of some use.

Thanks, I'll take a look and get back if Solved.

Oooooooooooh!!! Very nice. Need to spend more time, but looks feasible.

Hi DBY,

I see you have data where Year is by column with names underneath as rows. Could you share example with People Names in one column and Year in another column?

Name Year
Joe 2009
Bill 2009
Sam 2009
Mary 2010
Susan 2010
Jill 2010

Hello
Glad it's of some help. If I understand you correctly and you have your Names and Years laid out in adjacent columns, then it can still be done but it's a bit more elaborate. I' m not sure if all your names are sorted chronologically by year, so the examples in my new attachment (see sheet 2), take this into account. One way uses Array formulas, which I believe can have a perfomance hit with large amounts of data and are tricky to enter, the other uses a helper column and has no need for Array formulas. In my example the List Boxes are hooked up to use the Array method. I hope this is not too confusing it's a little difficult not knowing exactly how your data is laid out.

Regards
DBY

Originally Posted by DBY

Hello
Glad it's of some help. If I understand you correctly and you have your Names and Years laid out in adjacent columns, then it can still be done but it's a bit more elaborate. I' m not sure if all your names are sorted chronologically by year, so the examples in my new attachment (see sheet 2), take this into account. One way uses Array formulas, which I believe can have a perfomance hit with large amounts of data and are tricky to enter, the other uses a helper column and has no need for Array formulas. In my example the List Boxes are hooked up to use the Array method. I hope this is not too confusing it's a little difficult not knowing exactly how your data is laid out.

Regards
DBY

Hi DBY,

This worked out perfectly. It required some manipulation and a few entries that were not chronological by year had to be moved around, but all in all 85% of the solution was used. The remaining 15% was not needed as I had some elements already configured or opted not to use (Name name with =INDEX(Sheet2!$M$2:$M$7,1,1):INDEX(Sheet2!$M$2:$M$7,COUNTIF(Sheet2!$M$2:$M$7,">"""),1)).

By the way, I used the array option as this was easier and more efficient.

Thanks so much and I am glad you found my question on this awesome forum.

Best Regards,

Tom