• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cascading list without data validation?

G

gmelchor

New Member
Hello all,


I have another tough question that I'm hoping the ninjas can help me crack.


I have a list of States and a list of Cities. My client doesn't want to use a drop-down box (says there are too many options, making drop-down box unappealing), thus cancelling out data validation techniques.


I have successfully set up the state list using a scroll bar similar to how it is outlined in this article: http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/.


Is there a way for me to make a second scroll bar that populates dependent on what I select in the first scrollbar? I know how to use VBA to record my selection from the states list.


If this isn't possible, is there a better way that doesn't use data validation?


Thanks again and I look forward to a response!
 
Hi, gmelchor!

Give a look at this link:

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list

The technique is used for data validation but you could adapt it to a simple list range.

Regards!
 
gmelchor...the problem is, the first scrollbar doesn't actually select anything.


It's a bit hard to offer alternatives, because you haven't given us any indication of how your data is set out. Suggest you post a link to a sample file. But meanwhile, here's a couple of suggestions:

1. Use pivottables and slicers. Problem is, slicers can take up quite a bit of screen realestate

2. Use the advanced filter and a bit of VBA. Something like the approach at http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html and in particular check out the file partial_match_lookup_optimized_excelhero.com.xls that is listed under the first 'Update' bit on that blog. You could use two of these 'freetext' fields to accomplish what you need. If you post a sample file, I'll even help you code it up.
 
Hey gmelchor...it would have been helpful had you mentioned your similar post on this matter at http://chandoo.org/forums/topic/clear-data-validation-list-with-new-selection-vba
 
Thank you for the quick responses. Maybe this sheet will help illustrate my point.


https://www.dropbox.com/s/eztk8zkqz675aew/lists.xlsx
 
Jeffreyweir,


That is a similar post, but I didn't think they were similar enough to warrant a mention. I was using data validation and wanted to clear my previous entries. This time I don't want to use data validation due to its restrictions on displaying the list.


(Thanks again for your help on that post though!)
 
I don't understand how the scrollbar is any different than data validation. In fact, a data validation list is just a scrollbar that conveniently collapses when not needed. With scrollbars, you still have exactly the same amount of items in each list, you still have to scroll through them. Even worse, the scrollbar approach takes up multiple cells, whereas the DV approach only takes up one.


Say you proceed with the scrollbar approach. How do you want to select a state from the first list...by someone clicking on one of the four cells? And do you need just one chosen city from the 2nd scrollbar to feed the dashboard? How? Or do you need multiple cities to feed something? And how do you expect users to select a particular city from that 2nd scrollbar?
 
Good points jeffreyweir. I would use DV, but my client doesn't like the format - making my job a lot more difficult.


When I click on a State from the first list I want it to feed into a cell. I already have the VBA down for this, it's the same as Chandoo uses in many of his dashboards. I would use the same technique for selected a City.


Only one chosen city would feed the dashboard. It might be cool to select multiple cities to feed to dashboard, but I think that is too complex for now.


What are your thoughts? Is this feasible or should I convince them that a DV is the only choice?
 
I'd ask your client the same thing I asked you: how the scrollbar is any different than data validation.


I have something in mind that I'll play with and post a link, but it's no better than DV in terms of making it easier on users.
 
I think they just like the idea of being able to see the drop-down items without actually having to click the drop-down, if that makes any sense.
 
Hi, gmelchor!


Give a look to this file:

https://dl.dropboxusercontent.com/u/60558749/Cascading%20list%20without%20data%20validation_%20%28for%20gmelchor%20at%20chandoo.org%29.xlsm


It has a state list in column A, N city lists from column 2 thru N+1, a vertical scroll bar for states in columns W:X, a vertical scroll bar for cities in columns AA:AB, and displays selected state & city (orange shaded) in ranges X1:Y1 and AB1:AC1 respectively (yellow shaded).


It uses 4 dynamic named ranges and 1 fixed named range:


StateList: =DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;1) -----> in english: =OFFSET(Hoja1!$A$2,,,COUNTA(Hoja1!$A:$A)-1,1)


StateSelectableList: =DESREF(Hoja1!$W$2;;;CONTARA(Hoja1!$W:$W)-1;1) -----> in english: =OFFSET(Hoja1!$W$2,,,COUNTA(Hoja1!$W:$W)-1,1)


CityList: =DESREF(INDIRECTO(DIRECCION(2;COINCIDIR(StateSelected;StateList;0)+1;1;1));;;CONTARA(DESREF(INDIRECTO(DIRECCION(2;COINCIDIR(StateSelected;StateList;0)+1;1;1));;;CONTARA(INDIRECTO(IZQUIERDA(DIRECCION(2;COINCIDIR(StateSelected;StateList;0)+1;1;1);HALLAR("$";DIRECCION(2;COINCIDIR(StateSelected;StateList;0)+1;1;1);2)-1)&":"&IZQUIERDA(DIRECCION(2;COINCIDIR(StateSelected;StateList;0)+1;1;1);HALLAR("$";DIRECCION(2;COINCIDIR(StateSelected;StateList;0)+1;1;1);2)-1)))-1;1));1) -----> in english: =OFFSET(INDIRECT(ADDRESS(2,MATCH(StateSelected,StateList,0)+1,1,1)),,,COUNTA(OFFSET(INDIRECT(ADDRESS(2,MATCH(StateSelected,StateList,0)+1,1,1)),,,COUNTA(INDIRECT(LEFT(ADDRESS(2,MATCH(StateSelected,StateList,0)+1,1,1),SEARCH("$",ADDRESS(2,MATCH(StateSelected,StateList,0)+1,1,1),2)-1)&":"&LEFT(ADDRESS(2,MATCH(StateSelected,StateList,0)+1,1,1),SEARCH("$",ADDRESS(2,MATCH(StateSelected,StateList,0)+1,1,1),2)-1)))-1,1)),1)


CitySelectableList: =DESREF(Hoja1!$AA$2;;;CONTARA(Hoja1!$AA:$AA)-1;1) -----> in english: =OFFSET(Hoja1!$AA$2,,,COUNTA(Hoja1!$AA:$AA)-1,1)


StateSelected: =Hoja1!$Y$1


Formulas:


a) State selection

W2: =DESREF(A$1;X$1+FILA()-2;0) -----> in english: =OFFSET(A$1,X$1+ROW()-2,0)

X1: vsbState linked cell

Y1: =INDICE(StateList;X1) -----> in english: =INDEX(StateList,X1)


b) City selection

AA2: =DESREF(INDIRECTO(DIRECCION(1;X$1+1;1;1));AB$1+FILA()-2;0) -----> in english: =OFFSET(INDIRECT(ADDRESS(1,X$1+1,1,1)),AB$1+ROW()-2,0)

AB1: vsbCity linked cell

AC1: =INDEX(CityList;AB1) -----> in english: =INDEX(CityList,AB1)


VBA code for worksheet (split if required if using different worksheets):

-----

[pre]
Code: 
Option Explicit

' constants
Const gksMainWS = "Hoja1"
Const gksMainList = "StateList"
Const gksMainSubList = "StateSelectableList"
Const gksDependentWS = "Hoja1"
Const gksDependentList = "CityList"
Const gksDependentSubList = "CitySelectableList"

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
' declarations
' start
If Application.Intersect(Target, Worksheets(gksMainWS).Range(gksMainList)) Is Nothing Then Exit Sub
' process
StateResizeCount
' end
End Sub

Private Sub vsbState_Change()
CityResizeCount
End Sub

Private Sub StateResizeCount()
' constants
' declarations
' start
' process
vsbState.Max = Worksheets(gksMainWS).Range(gksMainList).Rows.Count
' end
End Sub

Private Sub CityResizeCount()
' constants
' declarations
' start
' process
vsbCity.Max = Worksheets(gksDependentWS).Range(gksDependentList).Rows.Count
' end
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
You must log in or register to reply here.
Back
Top