#1
|
|||
|
|||
Dropdown list from a value of another cell in dynamic worksheet
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 |
#2
|
|||
|
|||
.
It would probably help get an answer if you posted a sample workbook of what you currently have. |
#3
|
||||
|
||||
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/
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#4
|
|||
|
|||
Lately was a thread with similar question in same forum here
https://www.msofficeforums.com/excel...another-2.html There were several solutions provided. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Dear Friend,
Kindly find attachment, It might be helpful. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dropdown select worksheet to point data? | ShankedS | Excel | 21 | 05-08-2018 11:09 AM |
Selection of a dropdown creates another dropdown wih the list | krishnamurthy.ka2810 | Word VBA | 1 | 04-26-2018 11:44 PM |
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet | CaptainRetired | Excel Programming | 18 | 01-04-2018 07:22 PM |
Have a cell filled black when blank in dropdown list | Aussie_81 | Excel | 14 | 04-06-2017 04:50 AM |
Change cell color everytime a value is selected in dropdown list | angelica_gloria | Excel | 4 | 01-27-2012 06:47 PM |