Dropdown list from a value of another cell in dynamic worksheet

[Javir]

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

[Logit]

.
It would probably help get an answer if you posted a sample workbook of what you currently have.

[Guessed]

You aren't the first person to want to do this and a google search (with the right terminology) will reveal several approaches.
http://lmgtfy.com/?q=excel+conditional+data+validation

This one looks good
https://www.excel-university.com/cre...ta-validation/

[ArviLaanemets]

Lately was a thread with similar question in same forum here
https://www.msofficeforums.com/excel...another-2.html
There were several solutions provided.

[Javir]

Thanks everyone,
you're better gold diggers than me! It's seems searching the forums is a harder task than learning VBA...
The links you provided solved my issue.

Regards,

Javir

[Shashi Kant]

Dear Friend,

Kindly find attachment, It might be helpful.