Excel conditional drop-down list using table ref.

I got this table of vehicles named VehicleList to maintain overview of active vehicles. All entries are formatted as text.

Now, I need a drop-down list with IDs of only active vehicles from VehicleList, e.g. when ACTIVE = 1

For now I know that placing the formula =INDIRECT("VehicleList[ID]") into Source gives me all the IDs from VehicleList.

But how do I include the conditional part?


The wanted result to following example is a drop-down list of IDs: 1, 3, 5

Can you upload an example file so we can work with that? See yellow banner at top of page for instructions.

The list needs to be a congruous list so I added a sheet2 and in B3 copied down as far as you need to

Formula:

=IFERROR(INDEX(VehicleList[ID],AGGREGATE(15,6,(ROW(VehicleList[ID])-ROW(Sheet1!$C$7)+1)/(VehicleList[ACTIVE]="1"), ROWS($A$3:$A3))),"")

This pulls all the IDs where the vehicle is active.
Then I created a defined dynamic name called "Actives"

Formula:

=Sheet2!$B$3:INDEX(Sheet2!$B$3:$B$50,SUMPRODUCT(--(LEN(Sheet2!$B$3:$B$50)>0)))

Then, your validation List "Refers to" =Actives
Does that work for you?

I see you have 365. You can use this formula instead of the long AGGREGATE formula
In Sheet2!B3 (it will copy down automatically)
=FILTER(VehicleList[ID],VehicleList[ACTIVE]="1")