Hi,



I have a travel itinerary worksheet that I reuse, so, sometimes, I have 10 rows (ex: 10 days) and sometimes less or more.
In one column, I have the name of the city where a client will spend the night. In the next column, I would like to have a drop-down list of the hotels from the specified city.
For example, if I choose London, in the next column, a drop-down list of the London hotels would be listed.

So far, I have accomplished it manually, by creating a list in a separate worksheet with a list name of London and then the name values of the hotels. In the itinerary worksheet I would select the cell where the dropdown list would show, then click on data validation, and finally on source, I'd use =INDIRECT(B2).

The problem is that the worksheet can be longer and I would need to data validate each cell from the dropdown column. Is there a way to tell the source in the Data Validation to look at the left cell and not to a specific one (ex:C2)? Or maybe, is there a way with VBA or not that can search for the non-empty cells from the left column and then assign a specific data validation for all cells in the dropdown column?

Sorry, the explanation might not make sense, specially after hours searching for a solution.

Thanks,

Javir