Excel VB Dynamic Dropdown Example

[cpclarke]

Hi guys, long time member but still only scratching around. But I want to create productive tools for work.

Q: I have a company contact list that is updated for each job based on location. I want to specify the state in the header which dynamically creates lists of those company in that state. Later I plan to use dependent dropdown using those named lists. Any help you can't give to create a VB script of hybrid solution to achieve this without using arm length formulas would be appreciated. Many thanks.

1. Firstly, I wanted to create a reference by concatenating “Trade” + “State” for to select state based companies. I will hide this column later.

=CONCATENATE($A7,$B$3)




2. Secondly, in the “Company” sheet I wanted to identify each company by which state they operated in, by creating an identical ID to step 1 on “Data Entry” sheet.

=IF(O3<>"",CONCATENATE($A3,O3),"")

=IF('Data Entry'!$B$7=INDEX(Company!$W2:$AD2,1,MATCH('Data Entry'!$B$7,Company!$W2:$AD2,0)),'Data Entry'!$B$7,"")


3. Thirdly, I want to create dynamic named lists with those company names. I think this step would be best done in VB but am open to suggestions. I need to add multiple company name who work in the same state (this will create the dynamic lists.

=IF(A2=Company!$AF$2,Company!$B$2,"")

 

[cpclarke]

I am sorry for the confusion. I am trying to keep the list simple. Simply I pick TRADE first. The dependent dropdown then displays companies based on trade selection (all good). If a company is in more than one state, then the STATE dropdown shows "NSW, QLD, VIC" etc. for that trade (all good but looks messy). I wanted to show just the selected STATE from header (eg: NSW) even if that company is also in other states. Sample spreadsheet at the dropbox link below. Look at Audio, Locksmith as examples.

Contact List-PC v14.xlsm

Shared with Dropbox

www.dropbox.com

Thanks again.

 

[Akuini]

I've downloaded the last file.
Sorry, it's too complicated to adjust the code to meet your requirements. The part where you have a company with multiple states on only one row makes it tricky.
Any particular reason why you don't break it down into multiple rows?

 

[cpclarke]

Thanks for responding Akuini,
To answer your question, my colleague wants only one company per line, so I am working with that. I have got the spreadsheet working the way I want, however I am using a combination of VB script and cell formulas. I am using a hidden column (with IF statements) to define the relevant "state" for the selection which I don't want unhidden or altered by the users. Using formulas that updated cell values makes it hard to lock down the spreadsheet as it prevents from working. The beauty of using VB code is that you can lock up the spreadsheet away from meddling fingers (more robust). As always, I appreciate you skills and support. Cheers, Phil

PS: I have attached Dropbox link in case you want to review

Contact List-PC v19.xlsm

Shared with Dropbox

www.dropbox.com

PS: Not sure how the XL2BB things works...

Contact List-PC v19.xlsm
	H
40
DATA ENTRY