Automatic, Dependent Cell Values

flackend · #1 08-25-2011, 12:49 PM

I have a spreadsheet that has an event start time in column A and the end time in column B.

I have it set it up so when the start time is filled in, the end time is automatically populated with the start time + two hours.

This formula is in each cell:

Code:

=IF(A1 <> "", A1 + TIME(2,0,0), "")

So the user set the start time as 5:30 PM and the end time is automatically 7:30 PM.

I also want the user to be able to set a custom end time. But if the user does, they have to erase the formula so it won't be there for the next semester of events.

I'm not sure if there is some form of data validation I can do? I could do brute-force dependent drop-down lists, setting one for every 5-minute interval, haha.

Catalin.B · #2 08-26-2011, 03:45 AM

a simple idea is to set a blank column for users to enter extra time if needed, and complete the formula with this column
=IF(A1 <> "", A1 + TIME(2,0,0), "")
=IF(A1 <> "", A1 + B1 + TIME(2,0,0), "")

flackend · #3 08-26-2011, 07:01 AM

I created another column, column C, with the start time + two hours formula:

Code:

=IF(A1 <> "", A1 + TIME(2,0,0), "")

Then I hid the column. Then set the data validation (list) for the end time column (column B) to this formula:

Code:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,1,1,1)

Which gives a drop-down list that contains only the contents of the cell one column to the right (which contains the start time + two hours).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dependent drop down lists in Word 2007	benbenasc	Word VBA	0	08-24-2011 06:34 AM
How to count multiple values in a single cell, except zero?	iuliandonici	Excel	1	04-13-2011 09:45 PM
Sum & difference between multiple values in a single cell	iuliandonici	Excel	4	04-13-2011 09:27 PM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM
How to create a Dependent Drop-Down Picklist	ComcoDG	Word	2	03-17-2009 07:57 AM