• 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.

macro to extract records from a dropdown list

L

lui de sagun

Member
Hi,

I have been asked to create a database query based on three (3) drop downs [Sheet1(view)]. The 3 dropdowns should be inter-related, such that the record on the 2nd dropdown should be based on the items selected from the first dropdown, and the 3rd dropdown should be based on the items selected from the previous dropdowns. My data is located [Sheet3(data)]. I tried to look for something in the net and found sample excel file in your forums but I could not upload it in this thread (anyway, file name is Excel-As-Database-demo.xlsm). This is close to perfect to my needs but I still have to do a little more tweaking because I am required to extract a single data based from the 3 dropdowns. I hope you can help me with this...
 
Hi Kanti,

Both - my problem is extracting filtered data based on the record selected from the 3 dropdowns.

All dropdowns (Product, Region and Customer Type) retrieves all records. I want to only see filtered records from the 2nd dropdown (Region) based on the item I chose from the 1st dropdown (Product) and the 3rd dropdown (Customer Type) based from the item I chose from the 2nd dropdown.

As seen on the sample below, say if I chose Desktop (Product) then Mid-West (Region) then Individual (Customer Type), the result will be Agent Bond (Agent ID)

Product Region Customer Type Agent ID
Desktop Mid-west Individual Agent Bond
Laptop West SME Agent Mary
Misc. North Individual Agent Neo
Misc. West Large Corporate Agent Vinod
 
Thanks Kanti but not quite close to what I actually need. I attached the file that I am working on (trimmed down the data so that I can upload it). In the view sheet, when I choose Desktop from Product, data from Region should only consist of East, Mid-West, North, South and West. Then if I selected East from Region, data from Customer Type should only consist of Individual.

I hope I was able to explain it clearly :)
 

Attachments

Hi, lui de sagun!
The uploaded file (downloaded from this site, I don't know if tweaked or as it was) just does the job, doesn't it?
Regards!
 
yes, this is the closest sample from what I need but I still want a little more filtering from the 3 dropdowns. If you may have noticed, what the dropdowns currently do is that all of them extract all the records. What I actually want to do is that in the view sheet, there are 3 dropdowns (Product, Region, Customer Type). When I choose 'Desktop' from Product, data from Region should only consist of 'East', 'Mid-West', 'North', 'South' and 'West'. Then if I selected 'East' from Region, data from Customer Type should only consist of 'Individual'.
 
Hi, lui de sagun!

Giving a 2nd look to your posted sample file, the solution is tweaking the procedure cmdUpdateDropDowns_Click at worksheet View (Sheet1), making it keep the yet selected filters (none at start time) until the Reset button is pressed, i.e., don't considering them independently but all together.

Apparently no need to get involved with dependent drop down boxes since it's all done by building the proper SQL statement as it's yet done at the beginning of procedure cmdShowData_Click for showing the data.

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