Dynamic Dropdown Lists in Excel Based on Dependent Table Values

nenandi

New Member
Joined
Dec 17, 2020
Messages
39
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
I have an Excel file with two tables: tbl_ZAM with columns [ZAM broj], [Opseg 1], and [Opseg 2], and tbl_Stamps with the column [ZAM broj]. In tbl_Stamps, I select values from a dropdown list that pulls data from the [ZAM broj] column in tbl_ZAM. Now, based on the selected ZAM broj, I want the dropdown list in the Opseg column to offer values from both [Opseg 1] and [Opseg 2] of the corresponding ZAM broj.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Obviously we have no idea what your data layout looks like, but I think the following example might point you in the right direction:

ZAM.xlsx
ABCDEF
1ZAM brojOpseg 1Opseg 2ZAM brojOpseg
2Zb1Op1 row1Op2 row2Zb1Op1 row1
3Zb2Op1 row2Op2 row2Zb3Op1 row3
4Zb3Op1 row3Op2 row3Zb2Op2 row2
5
Sheet1
Cells with Data Validation
CellAllowCriteria
E2:E5List=$A$2:$A$4
F2:F5List=OFFSET(INDEX($A$2:$A$4, MATCH($E2,$A$2:$A$4,0)),0,1,1,2)
 
Upvote 0
Slight adjustment
ZAM.xlsx
ABCDEF
1ZAM brojOpseg 1Opseg 2ZAM brojOpseg
2Zb1Op1 row1Op2 row1Zb1Op1 row1
3Zb2Op1 row2Op2 row2Zb3Op1 row3
4Zb3Op1 row3Op2 row3Zb2Op2 row2
5
Sheet1
Cells with Data Validation
CellAllowCriteria
E2:E5List=$A$2:$A$4
F2:F5List=OFFSET(INDEX($A$2:$A$4, MATCH($E2,$A$2:$A$4,0)),0,1,1,2)
 
Upvote 0
Here are images: when I choose [ZAM broj] in column L, the dropdown list in column M for [Opseg] should display [Opseg 1] and [Opseg 2].
 

Attachments

  • Screenshot 2025-02-04 132451.png
    Screenshot 2025-02-04 132451.png
    1.7 KB · Views: 2
  • Screenshot 2025-02-04 132335.png
    Screenshot 2025-02-04 132335.png
    3.6 KB · Views: 2
  • Screenshot 2025-02-04 132305.png
    Screenshot 2025-02-04 132305.png
    10.8 KB · Views: 2
Upvote 0
As far as I can tell from your images, that's pretty much what I gave you. Unfortunately, I'm going to be away from my laptop for over a week now (holiday) so if anyone else on the forum feels like stepping in to assist they're more than welcome.
 
Upvote 0

Forum statistics

Threads
1,226,266
Messages
6,189,936
Members
453,583
Latest member
Ok_category1816

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