Replace validation drop-down

Columns C and D use validation drop-downs to figure out the time to place in Column E .  This has turned out to be a somewhat confusing process that I am hoping to simplify.

I would prefer to replace the drop-down validation of both columns with a single column that allows the UTC offset (such as -5 or -6) to be input manually.  One would simply 'type' instead of 'select' the number +6, -5, +3 or whatever manually, and the time could populate properly.

The sheet is editable by anyone and assistance is greatly appreciated.
http://spreadsheets.google.com/ccc?key=tfyW71dycfBcBbychI0X3aA&authkey=COOO6JsO#gid=0
rtod2Asked:
Who is Participating?
 
SiddharthRoutCommented:
I have removed the Validation.

You can also do that by Highlighting the column and then clicking on the menu Tools~~> Data validation and then click on "Remove Validation"

Sid
0
 
rtod2Author Commented:
Thank you Sid but just removing it renders the sheet useless.  I was looking to get rid of Column D entirely 'replace' the drop-down validation with an manual procedure.  Entering a + or - value would adjust the time as needed from the UTC time.  Assistance is greatly appreciated.
0
 
TommySzalapskiCommented:
This formula will get you the time zone if D14 is the +6, -5, +3 column. You could make the column grey so people can tell not to type in it.
=index('Sheet3'!A:A,match(D14,'Sheet3'!B:B,false))
0
 
rtod2Author Commented:
Would you mind showing me an example?  Deleting either of those two columns causes an error.  I do not want the multiple columns suggested.  Only one is fine.  I only want to input the +2 or -2 and have the time populate.  
0
 
rtod2Author Commented:
follow-up on the way
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.