Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-05-2018, 03:13 PM
Javir Javir is offline Dropdown list from a value of another cell in dynamic worksheet Mac OS X Dropdown list from a value of another cell in dynamic worksheet Office 2010 64bit
Novice
Dropdown list from a value of another cell in dynamic worksheet
 
Join Date: Mar 2018
Posts: 4
Javir is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-05-2018, 09:53 PM
Logit Logit is offline Dropdown list from a value of another cell in dynamic worksheet Windows 10 Dropdown list from a value of another cell in dynamic worksheet Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
It would probably help get an answer if you posted a sample workbook of what you currently have.
Reply With Quote
  #3  
Old 06-05-2018, 10:33 PM
Guessed's Avatar
Guessed Guessed is offline Dropdown list from a value of another cell in dynamic worksheet Windows 10 Dropdown list from a value of another cell in dynamic worksheet Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #4  
Old 06-05-2018, 10:38 PM
ArviLaanemets ArviLaanemets is offline Dropdown list from a value of another cell in dynamic worksheet Windows 8 Dropdown list from a value of another cell in dynamic worksheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Lately was a thread with similar question in same forum here
https://www.msofficeforums.com/excel...another-2.html
There were several solutions provided.
Reply With Quote
  #5  
Old 06-06-2018, 12:36 PM
Javir Javir is offline Dropdown list from a value of another cell in dynamic worksheet Mac OS X Dropdown list from a value of another cell in dynamic worksheet Office 2010 64bit
Novice
Dropdown list from a value of another cell in dynamic worksheet
 
Join Date: Mar 2018
Posts: 4
Javir is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 06-07-2018, 02:52 AM
Shashi Kant Shashi Kant is offline Dropdown list from a value of another cell in dynamic worksheet Windows 7 32bit Dropdown list from a value of another cell in dynamic worksheet Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

Dear Friend,

Kindly find attachment, It might be helpful.
Attached Files
File Type: xlsx Query.xlsx (9.7 KB, 11 views)
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft