List Box that can change with Radio Buttons

[PaulF]

Hello all,

I posted my current project/dash board recently and was tasked with turning it from AMER only to AMER/LATM/EMEA/APAC (Global). This would add 200+ queues and make this one page summary way to long... So my thought was add some radio buttons to the top of the List Box that change the List Box selections (without Macro's/VBA because I have no skill there... yet )

I did up a quick of concept concept... and figured it would be good to share as it might help others learn... and others more skilled, might have give me other ideas, or better ways to handle this...

Any feedback including Hey Knuckle Head, why are you doing it that way... are always welcome

./paulf

 

[Luke M]

Thanks for sharing PaulF. My next challenge would be to figure out how to make it so that the ListBox doesn't show extra blank rows at end.

 

[PaulF]

Luke M... Old buddy... old pal... Not even a hint or a clue as to where I should look ?? Can I at least buy a vowel ???

 

[Luke M]

Well, ok. You'll want to create a dynamic named range.

 

[PaulF]

Luke... I tried everything I could think of and google... My ListBox is Static in size and snickering at me... *shrug*

 

[Nebu]

May be something like this

 

[PaulF]

=IF(Sheet3!$D$1=1,OFFSET(Sheet3!$C$4,0,0,COUNTA(Sheet3!$F$4:$F$54),1),IF(Sheet3!$D$1=2,OFFSET(Sheet3!$C$4,0,0,COUNTA(Sheet3!$I$4:$I$54),1),IF(Sheet3!$D$1=3,OFFSET(Sheet3!$C$4,0,0,COUNTA(Sheet3!$L$4:$L$54),1))))

hmmmmm... Just when I start to think I have a clue about excel... another layer of the mask is peeled back... I never thought about using a formula in a name array...

Holy Schnikeys !! Still so much to lean... but I'm loving this journey...

Thank you again Luke

./bow

 

[Luke M]

It's the same for all of us, Paul. Even us ninjas learn something new every day.

Here's another way to setup the dynamic range, with a bit shorter formula and no volatile functions.

 

[John()]

Hi PaulF was reading your ... 6-7 months later ... post and was very impressed ...very nice to be able to create dashboards that look like what you did and only learning for such a short time.
Hope to be doing things like that myself soon ..... at the moment im hooked on formulas and love looking up different formulas used on this site and from reading the Chandoo blog .... and then analizing them to see how they work ... but have to start looking at Dashboards as hopefully next year if i learn enough i might find myself a job in a excel related field ....

Anyway then saw this thread downloaded it to have a look ... and again very nicely done.
Have just one question seeing as you ask for feedback ... Why the vlookup formula .... as most things i read about vlookup is its volatile and if you can use something else do ... now you might be baseing this project on something that wont ever be big enough that lots of vlookup slow things down so in that case i understand ..
but me personally if i can use something different wheather the project would be big or small i would use it and only use vlookup and others like offset if i couldnt find a similar solution.
in this case i manged to get a index formula that dose the job

=IF(INDEX(E17:$M$54,$B$4,$G$1)=0,"",INDEX(E17:$M$54,$B$4,$G$1))
slapped a if on the front to get rid of the 0 for the shorter lists.

Anyway thanks for posting as i love going through things and seeing how they work and learning from them ... off now to have a look at the others solution to the blank rows.

 

[John()]

ok looked at luke M solution .... very nice ...works a treat ... all you need to do then is adjust the size of the combo box for the small list ... because even with the code the small list shows blanks due to the size of the combo box.