Using VBA to automate dependent dropdowns.

B

Benero

New Member
Joined
Jun 13, 2022
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Can Macros be used to go through a lists in one worksheet (A) to change four dropdowns on another worksheet (B)?
Dropdowns 3 and 4 are dependent on number 2, and the number of rows on worksheet A can be over 100 at times.

New to VBA and coding and literally do not know if it can be done and where to start, any help or advice greatly appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Fat answer needs visual help.
try to attach a mini sheet via XL2BB tool
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Hi bebo02199.

Thank you so much for replying.
Keep getting an error about merged cells so had to do Table only, hope it can still help you to help me.


This is the main dashboard that variables are changed manually at the moment....Worksheet B in question.
02/04/2022
20:00:00€
League
Algeria Ligue 1
Home Team
Avai€
Away Team0
Fortaleza€


This sheet contains the info to change on dashboard...Worksheet A
TB-CGM Football Database Dec 21.xlsm
ABCDE
1DateLeagueTimeHomeAway
217/06/2022Algeria Ligue 118:00:00ES SetifUSM Alger
317/06/2022China League One08:30:00Jiangxi L.Shaanxi Changan
417/06/2022China League One09:00:00Nantong ZhiyunGuangxi Baoyun
517/06/2022China League One09:00:00Suzhou DongwuQingdao Youth I
617/06/2022China League One12:30:00Beijing BGXinjiang T.
717/06/2022China Super League11:30:00Shenzhen FCShijiazhuang
817/06/2022China Super League13:30:00MeizhouBeijing
919/06/2022China Super League11:30:00Wuhan Three TownsShanghai SIPG
1019/06/2022China Super League13:30:00Guangzhou R&FHebei China
1120/06/2022China Super League11:30:00Changchun YataiHenan
1220/06/2022China Super League11:30:00Chengdu Better CityMeizhou
1320/06/2022China Super League13:30:00Guangzhou EvergrandeZhejiang Gt.
1420/06/2022China Super League13:30:00Shanghai ShenhuaWuhan
1519/06/2022Norway 1st Division15:00:00StartStabaek
1619/06/2022Norway 1st Division15:00:00Stjordals/BlinkBrann
1720/06/2022Norway 1st Division18:00:00AasaneRanheim
1820/06/2022Norway 1st Division18:00:00FredrikstadRaufoss
1920/06/2022Norway 1st Division18:00:00KongsvingerGrorud
2020/06/2022Norway 1st Division18:00:00SandnesKFUM Oslo
2120/06/2022Norway 1st Division18:00:00SkeidSogndal
2218/06/2022Norway Eliteserien16:00:00SarpsborgStromsgodset
2318/06/2022Norway Eliteserien18:00:00KristiansundBodo/Glimt
2419/06/2022Norway Eliteserien18:00:00JervHam-Kam
2519/06/2022Norway Eliteserien18:00:00LillestromRosenborg
2619/06/2022Norway Eliteserien18:00:00OddMolde
2719/06/2022Norway Eliteserien18:00:00TromsoeHaugesund
2819/06/2022Norway Eliteserien18:00:00VikingSandefjord
2919/06/2022Norway Eliteserien20:00:00AalesundValerenga
3017/06/2022Ireland First Division20:45:00Bray WanderersGalway Utd
3117/06/2022Ireland First Division20:45:00Cobh RamblersWexford
3217/06/2022Ireland First Division20:45:00Treaty UnitedAthlone Town
3318/06/2022Ireland First Division20:30:00Longford TownCork City
3418/06/2022Japan J1 League11:00:00Jubilo ITosu
3518/06/2022Japan J1 League11:00:00KashimaKyoto Sanga
3618/06/2022Japan J1 League11:00:00ShimizuAvispa F
3718/06/2022Japan J1 League11:00:00ShonanTokyo
3818/06/2022Japan J1 League12:00:00GambaYokohama Marinos
3918/06/2022Japan J1 League12:00:00KashiwaKobe
4018/06/2022Japan J1 League12:00:00KawasakiSapporo
4118/06/2022Japan J1 League12:00:00HiroshimaCerezo
4218/06/2022Japan J1 League12:00:00UrawaNagoya
4318/06/2022Japan J2 League07:00:00Ichihara ChibaOmiya A
4418/06/2022Japan J2 League11:00:00Grulla MoriokaTokushima V
4518/06/2022Japan J2 League11:00:00Roasso KMito H
4618/06/2022Japan J2 League11:00:00TochigiOita
4718/06/2022Japan J2 League11:00:00SendaiYokohama FC
4818/06/2022Japan J2 League11:00:00Tokyo VRenofa Y
4918/06/2022Japan J2 League12:00:00MontedioVentforet
5018/06/2022Japan J2 League12:00:00Fagiano ORyukyu
5118/06/2022Japan J2 League12:00:00V-VarenKusatsu G
5219/06/2022Japan J2 League07:00:00NiigataBlaublitz
5319/06/2022Japan J2 League11:00:00Machida ZZweigen K
5417/06/2022Korea Lge 112:00:00Gimcheon SangmuSuwon City
5517/06/2022Korea Lge 112:30:00Pohang SteelersGangwon
5618/06/2022Korea Lge 111:00:00Jeju UtdIncheon Utd
5718/06/2022Korea Lge 112:30:00SeongnamDaegu
5819/06/2022Korea Lge 111:00:00UlsanJeonbuk
5919/06/2022Korea Lge 100:00:00Suwon BluewingsSeoul
6018/06/2022USA MLS21:00:00SeattleLos Angeles
6118/06/2022USA MLS23:00:00LA GalaxyPortland
6219/06/2022USA MLS01:00:00New YorkToronto
6319/06/2022USA MLS01:30:00San JoseAustin FC
6419/06/2022USA MLS01:30:00PortlandAustin FC
6519/06/2022USA MLS01:30:00MontrealAustin FC
6619/06/2022USA MLS01:30:00ColumbusCharlotte
6719/06/2022USA MLS01:30:00OrlandoHouston
6819/06/2022USA MLS01:30:00PhiladelphiaCincinnati
6919/06/2022USA MLS02:00:00ChicagoWashington
7019/06/2022USA MLS03:00:00DallasVancouver
7119/06/2022USA MLS03:30:00Salt LakeSan Jose
7219/06/2022USA MLS22:00:00Atlanta UtdInter Miami
7319/06/2022USA MLS23:00:00New York CityColorado
7419/06/2022USA MLS00:00:00NashvilleKansas City
7520/06/2022USA MLS02:00:00New EnglandMinnesota
7617/06/2022Ireland Premier20:45:00Bohemians D.Shelbourne
7717/06/2022Ireland Premier20:45:00Derry CityDrogheda United
7817/06/2022Ireland Premier20:45:00DundalkShamrock
7917/06/2022Ireland Premier20:45:00St. PatricksUC Dublin
8018/06/2022Ireland Premier20:45:00Sligo RoversFinn Harps
8118/06/2022Brazil Serie A21:30:00Goianiense GOJuventude RS
8218/06/2022Brazil Serie A00:00:00CuiabaCeara
8319/06/2022Brazil Serie A02:00:00SantosBragantino
8419/06/2022Brazil Serie A21:00:00Atletico MineiroFlamengo
8519/06/2022Brazil Serie A21:00:00CorinthiansGoias
8619/06/2022Brazil Serie A21:00:00CoritibaParanaense
8719/06/2022Brazil Serie A23:00:00FortalezaAmerica FC MG
8819/06/2022Brazil Serie A23:00:00InternacionalBotafogo
8919/06/2022Brazil Serie A00:00:00FluminenseAvai
9017/06/2022Brazil Serie B01:00:00Vila Nova GOOperario Ferroviario
9117/06/2022Brazil Serie B00:00:00CriciumaBrusque
9218/06/2022Brazil Serie B02:30:00Brasil ALItuano
9318/06/2022Brazil Serie B16:00:00GremioSampaio Correa
9418/06/2022Brazil Serie B21:00:00Londrina PRVasco da Gama
9518/06/2022Brazil Serie B21:00:00NovorizontinoTombense
9618/06/2022Brazil Serie B23:30:00Nautico PERecife PE
9719/06/2022Brazil Serie B16:00:00Guarani FC SPAlagoano
9820/06/2022Iceland Urvalsdeild20:00:00FramVestmannaeyjar
9920/06/2022Iceland Urvalsdeild21:15:00BreidablikAkureyri
10020/06/2022Iceland Urvalsdeild21:15:00GardabaerKR Reykjavik
10118/06/2022Finland Veikkausliiga14:00:00ValkeakoskiMariehamn
10218/06/2022Finland Veikkausliiga14:00:00HonkaKuopion Palloseura
10318/06/2022Finland Veikkausliiga16:00:00HJKOulu
10418/06/2022Finland Veikkausliiga16:00:00TampereenLahti
10518/06/2022Finland Veikkausliiga16:00:00SeinajoenTurku
10618/06/2022Finland Veikkausliiga22:00:00HIFKVaasa
Upcoming Fixtures


Idea is to automate going row by row, calculate formulas for each and the results exported to another workbook. Already have macro that paste into destination workbook. Please let me know if you need any more info and thank you so much.
 
Upvote 0
Where are the cells with dropdown in worksheet B?
and where is the 3 and 4 and 2? How does it change?
 
Upvote 0
bebo021999 said:
Where are the cells with dropdown in worksheet B?
and where is the 3 and 4 and 2? How does it change?
Click to expand...
okay I have removed the merge cells and can share mini sheet :)

TB-CGM Football Database Dec 21.xlsm
BCDEFGHIJK
6
702/04/2022
820:00:00€
9
10League
11Algeria Ligue 1€
12
13Home Team
14Avai€
15
16Away Team0
17Fortaleza€
18
19
Dashboard
Cell Formulas
RangeFormula
H8,H17,H14,J11H8=HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
Cells with Data Validation
CellAllowCriteria
D14List=INDIRECT(SUBSTITUTE(C11," ","_"))
D17List=INDIRECT(SUBSTITUTE(C11," ","_"))
C11:I11List=Leagues
E8:G8List=Sheet2!$H$2:$H$194



Dropdowns are as follows:

1 - Time
2 - League ( master for dependents)
3 - Home
4 - Away


Thank you
 
Upvote 0
Could you share file via google drive? do not forget to eliminate sensitive data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Dropdown list that is not data validation
Replies
1
Views
646
delaney1102
D
O
  • Solved
Save PDF's from a dropdown menu using VBA
2
Replies
12
Views
383
ob1tech77
O
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
445
AndyPandy27
A
D
  • Question
Create Dependent Check Box Selection
Replies
2
Views
82
Dokat
D
W
  • Question
Macro for worksheet by row
Replies
1
Views
237
rollis13
rollis13

Forum statistics

Threads
1,215,112
Messages
6,123,162
Members
449,099
Latest member
afishi0nado

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