Excel VB Dynamic Dropdown Example

C

cpclarke

New Member
Joined
Jun 6, 2016
Messages
10
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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.

Excel Dynamic List 1.png




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


Excel Dynamic List 2.png


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

Excel Dynamic List 3.png


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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
See if this helps:
I shared a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might be difficult to maintain as it requires lots of tables & lots of helper columns. This version uses vba, you only need 1 table, 1 helper column & 1 named range. The code is a bit complicated but easy to set up and maintain.

Also check post #12, it's a different method where the macro will create a helper table which will be used as the source of data-validation.

www.mrexcel.com

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Many thanks Akuini, you are amazing.
I have played with your code today and it is a joy to use.

My Sheet 1 has a State selection cell in the header, which updates the State column in all the rows (eg: $a6=$F$2). The default state of "Florida" becomes the default but I can still select from the dropdown (and your existing code caters for this).

My question is: My company listing (Sheet2) has one company per row (I don't want multiple rows for the same company). Where a company operates in multiple States (eg: Arizona, California & Florida), I want to have the option to select another State if exist for that company (else leave as default). I have multiple State columns per company row (State1 "A:A", State2 "B:B", State3 "C:C", etc). How can your code be modified to cater for if exists multiple states?

I hope this makes sense. Any advice much appreciated.

Many thanks, Phil
 
Upvote 0
cpclarke said:
I have played with your code today and it is a joy to use.
Click to expand...
Which code are you using? the first code (in post 1) or the second code (post 12)?
cpclarke said:
My question is: My company listing (Sheet2) has one company per row (I don't want multiple rows for the same company). Where a company operates in multiple States (eg: Arizona, California & Florida), I want to have the option to select another State if exist for that company (else leave as default). I have multiple State columns per company row (State1 "A:A", State2 "B:B", State3 "C:C", etc).
Click to expand...
Could you show us an example of the table?
Better yet, could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
 
Upvote 0
Hi there, Im sorry I dont know whcih VB version I have used.

Sheet1 Mini sheet

Contact List-PC v13.xlsm
ABCDEF
1STORE:Walmart
2LOCATION:Sydney
3STATE:NSW
4JOB NUM:2108009
5
6TRADESTATECOMPANYREPPHONEEMAIL
7CleaningNSWStatewide Quality ServicesMonica(02) 9723 8822accounts@statewidequality.com.au
8ElectricalNSWElecon ElectricalSenad Sisic0409 366 116senad@elecon.com.au
9FlooringNSWBirrus Matting SystemsDavid Brown0434 233 435david@birrusmatting.com.au
10GlazingNSWCoastal Glass & Maintenance Pty LtdDan Johnston0407 178 454danjohnston@myacn.net.au
11JoineryNSWC&A JoineryClaude Pintos0419 016 049claude@cajoinery.com.au
12PaintingNSWAlliance Painting and Decorating - NorthBruno Smokrvic0411 429 365bsmokrovic@iinet.net.au
13NSW   
14NSW   
Sheet1
Cell Formulas
RangeFormula
D7:D14D7=IF($C7<>"",INDEX(Table1[#Data],MATCH(Sheet1!$C7,Table1[COMPANY],0),4),"")
E7:E14E7=IF($C7<>"",INDEX(Table1[#Data],MATCH(Sheet1!$C7,Table1[COMPANY],0),5),"")
F7:F14F7=IF($C7<>"",INDEX(Table1[#Data],MATCH(Sheet1!$C7,Table1[COMPANY],0),6),"")
B8:B14B8=$F$3
Cells with Data Validation
CellAllowCriteria
A7:C14List=xName
D7:F14Any value



Sheet2 mini sheet


Contact List-PC v13.xlsm
ABCDEFGHIJKLMN
1
2STATE1STATE2STATE3
3TRADECOMPANYSTATE1REP1PHONE1EMAIL1STATE2REP2PHONE2EMAIL2STATE3REP3PHONE3EMAIL3
4BuilderAdform ShopfittingWAAdrian0417 636 667adrian@adform.com.auWAAdrian0417 636 667adrian@adform.com.auWAAdrian0417 636 667adrian@adform.com.au
5BuilderAlliance Retail InteriorsNSWScott Perry0412 136 119Scott Perry0412 136 119Scott Perry0412 136 119
6BuilderArtisansSADavid Nehme0416 173 095david@artisans.net.auDavid Nehme0416 173 095david@artisans.net.auDavid Nehme0416 173 095david@artisans.net.au
7BuilderJigsaw InteriorsWAJohn Starzec0412 305 035QLDJohn Starzec0412 305 035QLDJohn Starzec0412 305 035
8BuilderNehcoSAJoe Nehme0412 433 374joe.nehme@highclassgroup.com.auJoe Nehme0412 433 374joe.nehme@highclassgroup.com.auJoe Nehme0412 433 374joe.nehme@highclassgroup.com.au
9BuilderPremier DisplaysNSWBrent Thomas0423 337 759brent@premierdisplays.net.auNZBrent Thomas0423 337 759brent@premierdisplays.net.auNZBrent Thomas0423 337 759brent@premierdisplays.net.au
10Cleaning1st Choice CleaningQLDSteve0405 621 471steve@cleaningcq.com.auVICSteve Smith0405 621 471steve@cleaningcq.com.auVICSteve Jones0405 621 471steve@cleaningcq.com.au
11CleaningAll Round Cleaning ServicesWADavid 0418 934 069allroundcleaning@bigpond.comDavid 0418 934 069allroundcleaning@bigpond.comDavid 0418 934 069allroundcleaning@bigpond.com
12CleaningAlways Clean AustraliaQLD1300 654 317alwaysclean@bmail.com1300 654 317alwaysclean@bmail.com1300 654 317alwaysclean@bmail.com
13CleaningBensons CleaningSAScott Hermsen0412 184 687bcs@bensconscleaning.com.auScott Hermsen0412 184 687bcs@bensconscleaning.com.auScott Hermsen0412 184 687bcs@bensconscleaning.com.au
14CleaningFinest Touch Cleaning Specialist Pty Ltd NSWHelen De Frenza 0410 106 158helen@finesttouchcleaning.com.auHelen De Frenza 0410 106 158helen@finesttouchcleaning.com.auHelen De Frenza 0410 106 158helen@finesttouchcleaning.com.au
15CleaningMastersonQLDJohn Masterson1300 921 331john@masterson.com.auJohn Masterson1300 921 331john@masterson.com.auJohn Masterson1300 921 331john@masterson.com.au
16CleaningMax Specialty Cleaning ServicesVICMax Talevski0433 402 155max@cleaningtothemax.com.auMax Talevski0433 402 155max@cleaningtothemax.com.auMax Talevski0433 402 155max@cleaningtothemax.com.au
17CleaningPolizar CleaningSANino0411 887 000polizarsg@adam.com.auNino0411 887 000polizarsg@adam.com.auNino0411 887 000polizarsg@adam.com.au
18ElectricalAliance ElectricalNSWCindy Eichmann0417 031 385office@alianceaust.com.auVICBob Eichmann0417 031 385office@alianceaust.com.auVICJohn Eichmann0417 031 385office@alianceaust.com.au
19ElectricalAotea ElectricalNZ0800 268 353info@aoteagroup.nzNTJohn 20800 268 353info@aoteagroup.nzNTJohn 30800 268 353info@aoteagroup.nz
20ElectricalASD ElectricalNSWAndrew Day0414 183 719 andrew@asdelectrical.com.auAndrew Day0414 183 719 andrew@asdelectrical.com.auAndrew Day0414 183 719 andrew@asdelectrical.com.au
21ElectricalBeda ElectricalNSWDaniel Bradbury0419 943 700naddan@bigpond .comDaniel Bradbury0419 943 700naddan@bigpond .comDaniel Bradbury0419 943 700naddan@bigpond .com
22ElectricalBenter ElectricalWASimon0404 467 450benterelectrical@bigpond.comSimon0404 467 450benterelectrical@bigpond.comSimon0404 467 450benterelectrical@bigpond.com
23ElectricalCable NetWASteve/Ray(08) 9381 5802steve@cablenet.com.auSteve/Ray(08) 9381 5802steve@cablenet.com.auSteve/Ray(08) 9381 5802steve@cablenet.com.au
24FlooringAlternative Surfaces (Ardit)NSWRichard DiBartolo0418 342 371 richardd@alternativesurfaces.com.au QLDRichard DiBartolo0418 342 371 richardd@alternativesurfaces.com.au QLDRichard DiBartolo0418 342 371 richardd@alternativesurfaces.com.au
25FlooringAsset Flooring Group Australia (Ardit, Vinyl, Carpet, )VICMax Dixon 0421 949 566contactus@assetflooring.com.auWAMax Dixon 0421 949 566contactus@assetflooring.com.auWAMax Dixon 0421 949 566contactus@assetflooring.com.au
26FlooringAZZI TilingNSWGeorge Azzi0411 643 555dgaholdings@optusnet.com.auGeorge Azzi0411 643 555dgaholdings@optusnet.com.auGeorge Azzi0411 643 555dgaholdings@optusnet.com.au
27FlooringBen Priest Vinyl FlooringVICBen Priestbenpriest@live.com.auBen Priestbenpriest@live.com.auBen Priestbenpriest@live.com.au
28FlooringBirrus Matting SystemsNSWDavid Brown0434 233 435david@birrusmatting.com.auQLDDavid Jones0434 233 435david@birrusmatting.com.auDavid Brown (NSW)0434 233 435david@birrusmatting.com.au
29FlooringCarpet Care Services Pty LtdNSWRoss Riek0411 129 989frontdesk@carpetcareservices.com.auRoss Riek0411 129 989frontdesk@carpetcareservices.com.auRoss Riek0411 129 989frontdesk@carpetcareservices.com.au
30FlooringCarpet ChoiceSAJenny Beckett(08) 8276 2420Edwardstown@carpetchoice.com.auJenny Beckett(08) 8276 2420Edwardstown@carpetchoice.com.auJenny Beckett(08) 8276 2420Edwardstown@carpetchoice.com.au
31GlazingA Grade Glass & AluminiumWAPeter0424 307 722admin@agradeglassandaluminium.com.auPeter0424 307 722admin@agradeglassandaluminium.com.auPeter0424 307 722admin@agradeglassandaluminium.com.au
32GlazingAAA GlazingVICGlenn Aisbett0429 442 618glenn@aaaglazingservices.com.auGlenn Aisbett0429 442 618glenn@aaaglazingservices.com.auGlenn Aisbett0429 442 618glenn@aaaglazingservices.com.au
33GlazingAB GLAZINGQLD(07) 4922 1997abglazin@bigpond.net.au(07) 4922 1997abglazin@bigpond.net.au(07) 4922 1997abglazin@bigpond.net.au
34GlazingAli-Tech GlassVICBrad (03) 9775 1988sales@ali-tech.com.auBrad (03) 9775 1988sales@ali-tech.com.auBrad (03) 9775 1988sales@ali-tech.com.au
35GlazingAll Points GlassVICAdam Smith(03) 9852 3002Adam@allpointsglassbulleen.com.auAdam Smith(03) 9852 3002Adam@allpointsglassbulleen.com.auAdam Smith(03) 9852 3002Adam@allpointsglassbulleen.com.au
36GlazingAUSCON PROJECTSVICAndrew(03) 4313 16257info@ausconprojects.com.auAndrew(03) 4313 16257info@ausconprojects.com.auAndrew(03) 4313 16257info@ausconprojects.com.au
37GlazingB & N GlassQLDGillian Holcombe (07) 4957 7477office@bnglass.com.auGillian Holcombe (07) 4957 7477office@bnglass.com.auGillian Holcombe (07) 4957 7477office@bnglass.com.au
38JoineryC&A JoineryNSWClaude Pintos0419 016 049claude@cajoinery.com.auClaude Pintos0419 016 049claude@cajoinery.com.auClaude Pintos0419 016 049claude@cajoinery.com.au
39JoineryDimensions Design & JoineryQLDEndre Gulyas0409 630 727endre@dimensionsdj.com.auEndre Gulyas0409 630 727endre@dimensionsdj.com.auEndre Gulyas0409 630 727endre@dimensionsdj.com.au
40JoineryMosh DCQLDJosh0405 360 655Josh0405 360 655Josh0405 360 655
41JoineryRW JoinersQLDRoss0429 497 003admin@rwjoiners.com.au Ross0429 497 003admin@rwjoiners.com.au Ross0429 497 003admin@rwjoiners.com.au
42JoinerySharp ShopfittingVICJames Cerra0404 054 700james@sharpshopfitting.com.auJames Cerra0404 054 700james@sharpshopfitting.com.auJames Cerra0404 054 700james@sharpshopfitting.com.au
43JoineryShop Fit CoQLDLachlan Waugh0432 359 136lachlan@shopfitco.com.auLachlan Waugh0432 359 136lachlan@shopfitco.com.auLachlan Waugh0432 359 136lachlan@shopfitco.com.au
44JoineryStatic JoineryQLDIvan(07) 5593 7739Ivan(07) 5593 7739Ivan(07) 5593 7739
45JoineryWoodlandsNZBrendanbrendan@woodlandshopfitting.co.nz Brendanbrendan@woodlandshopfitting.co.nz Brendanbrendan@woodlandshopfitting.co.nz
46Painting5 Star PaintingNSWPaul0417 018 399minshull@bigpond.net.auQLDPaul0417 018 399minshull@bigpond.net.auQLDPaul0417 018 399minshull@bigpond.net.au
47PaintingAA Koutts PaintingSAAndrew0412 834 200glennemslie@bigpond.comAndrew0412 834 200glennemslie@bigpond.comAndrew0412 834 200glennemslie@bigpond.com
48PaintingAJ Blunt PaintingSATony0412 822 112tony@ajbluntpaint.com.auTony0412 822 112tony@ajbluntpaint.com.auTony0412 822 112tony@ajbluntpaint.com.au
49PaintingAlliance Painting and Decorating - NorthNSWBruno Smokrvic0411 429 365bsmokrovic@iinet.net.auBruno Smokrvic0411 429 365bsmokrovic@iinet.net.auBruno Smokrvic0411 429 365bsmokrovic@iinet.net.au
50PaintingArgyll Painting & DecoratingQLDTom Porter0422 068 407tommypotter68@gmail.comTom Porter0422 068 407tommypotter68@gmail.comTom Porter0422 068 407tommypotter68@gmail.com
51PaintingBenae Restorations VICBen Saunders0400 935 400benaepainting@yahoo.com.auBen Saunders0400 935 400benaepainting@yahoo.com.auBen Saunders0400 935 400benaepainting@yahoo.com.au
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K5:K13,K15:K17,K20:K21Expression=#REF!="structural drawings"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="timber"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="shelving"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="security system"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="locksmith"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="hire equipment"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="hardware"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="tiling"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="render walls"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="project management"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="mechanical"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="plumbing"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="joinery"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="lighting"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="flooring"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="painting"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="building permit"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="brick supply"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="brick install"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="av"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="fire sprinkler"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="cleaning"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="electrical"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="plastering"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="signage"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="entry doors"textYES
K5:K13,K15:K17,K20:K21Expression=#REF!="glazing"textYES
K14,K19,K22:K23,K4Expression=#REF!="structural drawings"textYES
K14,K19,K22:K23,K4Expression=#REF!="timber"textYES
K14,K19,K22:K23,K4Expression=#REF!="shelving"textYES
K14,K19,K22:K23,K4Expression=#REF!="security system"textYES
K14,K19,K22:K23,K4Expression=#REF!="locksmith"textYES
K14,K19,K22:K23,K4Expression=#REF!="hire equipment"textYES
K14,K19,K22:K23,K4Expression=#REF!="hardware"textYES
K14,K19,K22:K23,K4Expression=#REF!="tiling"textYES
K14,K19,K22:K23,K4Expression=#REF!="render walls"textYES
K14,K19,K22:K23,K4Expression=#REF!="project management"textYES
K14,K19,K22:K23,K4Expression=#REF!="mechanical"textYES
K14,K19,K22:K23,K4Expression=#REF!="plumbing"textYES
K14,K19,K22:K23,K4Expression=#REF!="joinery"textYES
K14,K19,K22:K23,K4Expression=#REF!="lighting"textYES
K14,K19,K22:K23,K4Expression=#REF!="flooring"textYES
K14,K19,K22:K23,K4Expression=#REF!="painting"textYES
K14,K19,K22:K23,K4Expression=#REF!="building permit"textYES
K14,K19,K22:K23,K4Expression=#REF!="brick supply"textYES
K14,K19,K22:K23,K4Expression=#REF!="brick install"textYES
K14,K19,K22:K23,K4Expression=#REF!="av"textYES
K14,K19,K22:K23,K4Expression=#REF!="fire sprinkler"textYES
K14,K19,K22:K23,K4Expression=#REF!="cleaning"textYES
K14,K19,K22:K23,K4Expression=#REF!="electrical"textYES
K14,K19,K22:K23,K4Expression=#REF!="plastering"textYES
K14,K19,K22:K23,K4Expression=#REF!="signage"textYES
K14,K19,K22:K23,K4Expression=#REF!="entry doors"textYES
K14,K19,K22:K23,K4Expression=#REF!="glazing"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="structural drawings"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="timber"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="shelving"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="security system"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="locksmith"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="hire equipment"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="hardware"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="tiling"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="render walls"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="project management"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="mechanical"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="plumbing"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="joinery"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="lighting"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="flooring"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="painting"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="building permit"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="brick supply"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="brick install"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="av"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="fire sprinkler"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="cleaning"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="electrical"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="plastering"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="signage"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="entry doors"textYES
A23:C23,G5:G13,C20:C21,G20:G21,G15:G18,K18,A5:C18Expression=#REF!="glazing"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="structural drawings"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="timber"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="shelving"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="security system"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="locksmith"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="hire equipment"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="hardware"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="tiling"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="render walls"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="project management"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="mechanical"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="plumbing"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="joinery"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="lighting"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="flooring"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="painting"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="building permit"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="brick supply"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="brick install"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="av"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="fire sprinkler"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="cleaning"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="electrical"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="plastering"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="signage"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="entry doors"textYES
B23,G14,G19,C22:C23,G22:G23,G4,B14:C14,A19:C22,A4:C4Expression=#REF!="glazing"textYES
 
Upvote 0
Let's use an example from row 10 sheet 2
Cleaning1st Choice CleaningQLDSteve0405 621 471steve@cleaningcq.com.auVIC

In sheet1 col TRADE-STATE-COMPANY
First you choose TRADE (Cleaning) then choose what? STATE or COMPANY? then what?
 
Upvote 0
I first select TRADE. Then I choose STATE (it is defaulted to the header State). The dropdown still allows me to change the State. Once I have picked State, then the next dropdown displays only those companies that operate in that state only. The is only company record per row only (not multiple rows). If one company operates in multiple states (there are separate STATE columns for multiple STATES), then the State dropdown will display only those states relevant to that company. If I choose another State from the dropdown, then that REP/PHONE/EMAIL details will be shown in Sheet1.
I hope your smarts can decipher my intent. Many thanks again. Phil +61407705606
 
Upvote 0
I still don't get it:
Let's use the example in row 10 sheet 2.
In sheet1:
1. Select Trade > Cleaning
2. In col State, the dropdown will show this?:
QLD
WA
QLD
SA
NSW
QLD
VIC
SA
3. Pick QLD or QLD will be the default? What do you mean by "Then I choose STATE (it is defaulted to the header State)."
4. Dropdown in col Company will show only 1 item, i.e. "1st Choice Cleaning", then you choose it.
5. Now in col State the dropdown will show :
QLD
VIC

Is that correct?
Can you explain in more detail?
 
Upvote 0
I just downloaded your file. I think I got it now. Let me think about it. I'll come back tomorrow.
 
Upvote 0
You must log in or register to reply here.

Similar threads

carpetony
  • Solved
CountIf across columns, as an array functions, not concatenated.
Replies
2
Views
238
Eric W
Eric W
B
  • Solved
Dynamic dropdown list based on filtered table
Replies
4
Views
569
Georgiboy
Georgiboy
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
296
Jeffrey Mahoney
Jeffrey Mahoney
W
  • Solved
Combining Filter function with Indirect/Concatenate to look up from another worksheet
Replies
3
Views
236
Fluff
Fluff
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
588
MissDB101
M

Forum statistics

Threads
1,215,510
Messages
6,125,228
Members
449,216
Latest member
biglake87

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top