Wednesday, December 27, 2017

yogi_Create Row By Row DropDown List Of Cities Based On Name Of State

Google Spreadsheet   Post  #2330

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-27-2017
Data Validation - Change Values Based On A Constant
Hi,

I am needing to create a Google Sheet that will allow users to use a drop down box to assign a value.  This drop down should be limited based on one of the columns that will be provided to the end user.


Here is a link to a quick mockup of what I am trying to do. https://docs.google.com/spreadsheets/d/1-wfhVpHNxIqAIbSv8KW1Ig48keP8axgnt1zzBgLPYJw/edit?usp=sharing   
(obviously not the real information but the same mechanism)

For example, if the person is listed as NJ, only NJ cities will show up in the drop down and not all cities.

I can not use any solution that involves scripting, as 95% of the end users will not have a Google account to log in with.  The end users are as a whole not very tech savvy so I need to make this as "idiot proof" as possible (they'll otherwise just type in whatever city they want for example, and make the exercise moot)

I've seen tutorials that either require scripting or use User Input A to change Dropdown B.  There will be dozens of people in this file at once, and 4-6,000 entries over 8-10 different files so I cannot do anything that requires manually setting each cell.

Is there an easy way to do this?  Thanks!