formula using data validation

[Tuttamay77]

Any ideas on how I can use a formula and two different ranges of data
for lookup lists in the Data Validation function?

Here is the issue. The form I created is a basically a schedule with
lots of data validation lists. The most recent problem is that the
validation list needs to be customized for appointments made on a
Monday. Monday appointment times begin at 9:00 AM - 5:00 PM else
appointments made Tuesday - Friday, the start time is 7:00 or 7:30 AM -
5:00 PM. The appointment times consist of 30 minute blocks.

I am trying to standardize the data to keep it simple for the users. Is
this possible? Thanks in advance.

 

[Biff]

Hi!

It's not real clear what you want.

Can you rephrase your explanation?

About all I got out of your post is that Monday appointments have a
different time range than the other days of the week!

How does that relate to a drop down?

Biff

 

[Tuttamay77]

I am using data validation lists for a schedule to standardize the data.
The original schedule did not allow for easy reporting/billing.

A new schedule has been developed but there are several issues with it.
I would like to refer to different ranges while still using the data
validation lists. Also, the schedule appears in specific time blocks so
I want to make sure that they pick the times frames available from the
list. I also wanted each time to only be picked once per day. The
folks using this prefer to use Excel. Is this possible?

Attached are two sample files. I included both the original schedule
and a report - which may become a new schedule if I can get it to
work....I am open to any suggestions for either one.


+-------------------------------------------------------------------+
|Filename: test old cal.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4573 |
+-------------------------------------------------------------------+

 

[Gary Brown]

Try the attached.

The only problem with it, is if you select a date, which is not a
Monday, then select a time, then go back and change your date to a
Monday, the time will be left in place from the original entry, however
if you put in Conditional format to make it white on white when this
happens, this may solve this problem (if you feel this is sifficient).

The formula in C52 and D52, could be simplified to just typing the
values into the cells, rather than the formula, but at least this way
it allows you to insert/delete rows, without having to update the
formula.

Good Luck

G


+-------------------------------------------------------------------+
|Filename: test report.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4576 |
+-------------------------------------------------------------------+

 

[Tuttamay77]

Gary,

Thanks so much for the formulas & fix for my schedule. You rock! I am
going to look into changing the font to white if the day is switched
like you mentioned too.

Thanks again.
Melissa

 

[Gary Brown]

If you need any help with the conditional format (white on white), le
me know. The solution I gave you is over complicated and could be mad
simplier, but at least the way I have done it, you can follow it.

You don't really need to put the days of the week down, just change th
data validation to say if weekday = 1 then do an indirect to D58, els
do an indirect to C58. I did it the way I have, to demonstrate it. Tr
and have a go at doing it the way I describe.