Range, INDEX lookup; selecting dates and time/rooms for meetings

[dmcnab]

Good morning, loungers. I am not sure if this request for assistance is 'too big' for the lounge, but hopefully not. I am attaching a small zipped workbook. There are 3 sheets. In each Project worksheet (001 or 002), in column N, I need to be able to select a meeting date (MTG #1) and a time/room in column O (MTG #1 time/room). And do the same for columns P and Q. The worksheet 'MTG 1 and MTG 2' is a calendar showing each workday, with the time/room selections. If needed, people can call in and arrange meetings (date, time/room) for two different kinds of meetings (MTG 1 or MTG 2). The date and time/room have to be entered on the Project management sheet (Project 001, Project 002 etc--there will be 4 Project sheets).


I have been working on this for quite a while, using different variations of drop-down menus etc where a time/room 'disappears' (through conditional formatting) but that is only a partial solution because I have to jump back and forth between the Project sheets and the 'MTG 1 and MTG 2' sheet to search for availability. I have concluded that it may require something like a huge INDEX formula (??) combined with some type of conditional formatting, so that if a time/room is selected on any given day, the worksheet 'MTG1 and MTG 2' no longer shows that time/room on that day as being available (ie. once it is booked, it comes off the sheet). And, there is already some 'worksheet change event' code in each Project worksheet if that prevents you from adding more code to each worksheet (if that's part of the solution).


I have shown an example of how the dates and the time/room entries would look on Project 001 and on Project 002 (and have colored the selected dates & time/rooms on the 'MTG 1 and MTG2' sheet, for easy reference. Ideally, I would like to be able to click on a cell (say, Project 001 N25) and see the array of dates & time/room available and select a date and a time/room (say, Feb 8 for MTG #1 and 9:30 @ #821 for time/room)....and do it again for Project 001 P25 and select a date (eg. April 15) and a time/room (10:45 @ #1208)....and do the same type of thing for Project 002 (see dates and time/room selections on that worksheet) and as I make a time/room selection, that time/room 'disappears' so that it is not available to be selected again for that day. The extra tricky part is being able to do the same thing with Project 002 (see my samples of Feb 1, 10:45 @ #820 and March 10, 3:15 @ #1208).


I want like to be able to post the 'MTG 1 and MTG2' sheet (or print it and email it) daily so that people can see (somewhat in real time) what times/rooms are available for meetings before the call asking to book a date & and time/room. Thank you for any help, or suggestions, as to how I can accomplish this....and if anyone has a better way of handling this, please say so....ps: my file was uploaded through SpeedyShare (see link below)Password is gusizipitoma (but I think this is a delete p/w?)..anyway, hope you can get the attached .ls file....thanks


http://www.speedyshare.com/files/29408506/Sample_for_Chandoo.xls

 

[Luke M]

Instead of using conditional formatting, perhaps you could use data validation to limit the choices?


Rough idea:

1. You have an initial list of all rooms. (list a)

2. You then have a "list" of formulas, checking list a against the Project sheets to see if the room is still available. If not available, display blank. (list b)

3. (This step is for cleanliness) Use formulas to create a nice, sorted list from step 2. Use a named dynamic range possibly to define the size of this list. (list c)

4. On the sheet where you want to schedule things, use data validation settings to check against the named range you setup in step 3.


I know it's rough, but what do you think?

 

[dmcnab]

Hi Luke....the theory sounds good....can you make me a small example of how it would look & work? Thanks in advance....I had also thought about something like this:


Use some VBA code that would work like this: say I have to enter a date for MTG #1 -- could I put my cursor on cell (eg. N25) and then go to the 'MTG 1 and MTG 2' worksheet, double click on the date of choice (say, Feb 8, 2011 from column A) and have it copy that date to cell N25...and to enter the time/room in O25, I then put my cursor on O25, switch to 'MTG 1 and MTG 2' sheet, double-click on the time/room of choice (picking 9:30 @ #821 from date row of Feb 8) ) and have it copy that data to O25 (and then use cond format to make '9:30 @ #821' on the 'MTG 1 and MTG 2' worksheet be white-on-white, so it doesn't show as available on that date...?.....

 

[dmcnab]

Hi again Luke...I am trying to get a start on your suggestion, but need some more basic help with concatenating....I need to join data from col A with data from col B in order to combine dates & time/rooms....see attached file.....do you know how to do this and still have the date show up as a date (rather than the serial number for that day) ?


http://www.speedyshare.com/files/29415886/Sample_re_concatenate.xls

 

[Luke M]

Hi dmcnab,

Yeah, handling dates w/ concatentating can be tricky. We can use the TEXT function though to force an output we desire (even get rid of the day, if we just want month-year combo)

E.g.,

=TEXT(A2,"ddmmyyyy")&B2

or

=TEXT(A2,"ddmmyyyy h:mm"&B2


Does that help?

 

[dmcnab]

Hi Luke...yes that helps b/c I assume from your 1st reply that dates and time/rooms should be in 1 cell ? Are you able to give me an example of what you meant in your 1st reply, especially the code that will check the 'Project' sheets to see if a date/time&room are available? Thhanks in advance...

 

[Luke M]

dmcnab,

I think with little tweaking of wording, Debra's sheet here might be an excellent example/template:

http://blog.contextures.com/archives/2011/04/15/set-up-golf-tee-times-in-excel/#axzz1RocpqaP0


I think this post is probably the one I remembering when I came up with the rough idea layout. It uses a lot of the same principles at least...