Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula using data validation
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. -- Tuttamay77 ------------------------------------------------------------------------ Tuttamay77's Profile: http://www.excelforum.com/member.php...o&userid=33047 View this thread: http://www.excelforum.com/showthread...hreadid=528681 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula using data validation
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" wrote in message ... 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. -- Tuttamay77 ------------------------------------------------------------------------ Tuttamay77's Profile: http://www.excelforum.com/member.php...o&userid=33047 View this thread: http://www.excelforum.com/showthread...hreadid=528681 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula using data validation
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 | +-------------------------------------------------------------------+ -- Tuttamay77 ------------------------------------------------------------------------ Tuttamay77's Profile: http://www.excelforum.com/member.php...o&userid=33047 View this thread: http://www.excelforum.com/showthread...hreadid=528681 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula using data validation
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 | +-------------------------------------------------------------------+ -- Gary Brown ------------------------------------------------------------------------ Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084 View this thread: http://www.excelforum.com/showthread...hreadid=528681 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula using data validation
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 -- Tuttamay77 ------------------------------------------------------------------------ Tuttamay77's Profile: http://www.excelforum.com/member.php...o&userid=33047 View this thread: http://www.excelforum.com/showthread...hreadid=528681 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula using data validation
If you need any help with the conditional format (white on white), let me know. The solution I gave you is over complicated and could be made 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 the data validation to say if weekday = 1 then do an indirect to D58, else do an indirect to C58. I did it the way I have, to demonstrate it. Try and have a go at doing it the way I describe. G -- Gary Brown ------------------------------------------------------------------------ Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084 View this thread: http://www.excelforum.com/showthread...hreadid=528681 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data Validation - Custom - Formula | Excel Worksheet Functions | |||
HELP: Data > Validation ---List ----Formula | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |