The aim of this tip is to create a drop-down list to select a category in a cell A and provide in cell B another drop-down that will be based on your previous choice. We will make use of Data/Data Validation/List option and the INDIRECT function.

For the first drop-down list use the procedure depicted here.

Step 1 = Prepare range of cells containing the different lists.

Enter a list of departments and name this range of cell as Dep : Guadeloupe in B2, Guyana in C2, Martinique in D2 , Reunion in E2tc

: Guadeloupe in B2, Guyana in C2, Martinique in D2 , Reunion in E2tc In B3, B4, B5 etc. enter in alphabetical order, the names of the 34 towns of Guadeloupe: Abymes, Anse-Bertrand, Baie-Mahault, etc. Name this range of cell as Guadeloupe ]

] In C3, C4, C5 etc. enter in alphabetical order the names of the 22 towns of Guyana. Name this range of cell as [Guyana].

Repeat the operation accordingly for the other departments

Step 2 = Create the first drop-down list, that displays the name of the department.

In another sheet of your workbook, select cell B1

Go to the Data tab> Data Validation > Setting tab.

Under "Allow" select "list"

In the Source field enter " =Dep "

" Name cell B1 as "Department" (right-click on B3 > Name a range)

Create your second drop-down list