How to create an excel that change of drop down then the multiple lists will change automatically?

Scenario:

2 supplier available (Supplier A and Supplier B) and both suppliers provide hotel and tour.

Hotel supply by Supplier A: Hotel 1, Hotel 2, Hotel 3
Hotel supply by Supplier B: Hotel 2, Hotel 3, Hotel 4

Flight supply by Supplier A: Flight 1, Flight 2, Flight 3
Flight supply by Supplier B: Flight 2, Flight 3, Flight 4

How to create as below in excel where, when select Supplier A in drop list, Hotel will only show Hotel 1-3 and Flight will only show Flight 1-3:

Supplier:
(Supplier A/Supplier B in drop down)

Hotel* Flight* *Hotel 1-4 and Flight 1-4 shown in box
(Hotel 1/ (Flight 1/
Hotel 2/ Flight 2/
Hotel 3/ Flight 3/
Hotel 4) Flight 4)

Please advice if anyone know how.

Thanks.

Regards,
JL

Example when select Supplier A/ Supplier B
Almir's picture

Try to use Advanced Filter

Create table with columns: Supplier, Flight and Hotel. Then use Advanced Filter on Data tab.

If this is not suitable for you, it is possible to create interactive query from table created above, in a way that when you change supplier, you get appropriate flight and hotel data.

Let me know if you want further assistance.