locked
data filter with cascasing list to produce graphs RRS feed

  • Question

  • Hi

    I have a data of nearly 50thousand entries, which reads like Country, Region, State, Area, Business, Category, SubCategory in respective columns from A through G.

    I want to develop a user form (or in spread sheet itself) with drop down lists for each title shown above.
    but each should produce sub sequent lists rather than entire lists. (like cascading lists) eg: When I select Country, Regions in that country to be displayed, When I select Region from the filtered list, states in that region should be displayed... like wise respectively.

    at last we end up with some data, based on this data, I want to link this data to a chart.

    My plan is to write an sql statement like "SELECT * FROM <sheet or range name> WHERE COUNTRY = '" & cmbCountry.value & "'"

    but I did not succeed... I donot know if we can write direct sql within Excel.

    Please suggest any alternative way to do this or if sql can be used for the same

    Very many Thanks in Advance.
    Repath Athyala.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.


    • Edited by Repath Athyala Thursday, September 27, 2012 8:17 AM modified question
    Thursday, September 27, 2012 8:11 AM

Answers

All replies

  • You are describing, almost exactly, a Pivot Chart. There are limitations on what type of chart you can produce, but give it a try. You can also apply filters to your data set, and they behave the same way - only cascading values that exist are shown in subsequent filterings.
    Thursday, September 27, 2012 1:13 PM
  • Hi Bernie,

    Can you explain / guide me as to how can I do this...
    Also, can I write SQL statements in Excel sheet to get data into another sheet, if so how...

    Thanks in advance... for your concern.

    With Regards
    Repath Athyala.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    Friday, September 28, 2012 2:03 AM
  • There was a discussion about cascading comboboxes in this topic, using ADO object and SQL queries, and also an example workbook: http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/b1471e4d-3d63-444a-be0d-25d6142d24ac

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Tuesday, October 2, 2012 9:16 PM
  • thank you Bernie and Benzadeus for your link: Create Dependent Lists : http://www.contextures.com/xlDataVal02.html

    Love the Love that Loves the Love and Hate the Love that Loves the Hate.


    • Marked as answer by Repath Athyala Thursday, October 18, 2012 2:13 PM
    • Edited by Repath Athyala Thursday, October 18, 2012 2:23 PM included names of the people who helped me.
    Thursday, October 18, 2012 2:13 PM