#1
|
|||
|
|||
Multi-Variable Lookup help
I am working on a room scheduling spreadsheet.
on one tab I have a list of rooms and the times and days they are in use and who is using them. On the second tab is a more visual display of the information so you can see a rooms use at a glance. I've attached a sample. I want it to look at the data sheet, and if the room is in use at a specific date and time I would like it to put the name of the person on the schedule. IF Data! A:A=any of the variables in schedule! I:I AND Data! B:B=schedule! A2 AND Data! C:C= schedule! B1 THEN return Data! column D in cell schedule!B2 Would be nice but not essential to have it return "CONFLICT" if more than one match was found. I show a sample of what I want to look like on the schedule tab. I have tried a variety of IF(AND()) and VLOOKUP functions, but I am just not familiar enough with them to make them work, if they can. The data is pulled from a class scheduling program and I would love to be able to automate this! |
#2
|
|||
|
|||
Hi
You can actually do this with Outlook. You can set up your rooms as resources and then allow people to book rooms using Outlook. Your IT Support desk should be able to set this up for you. Hope this helps. OTPM |
#3
|
|||
|
|||
As an alternative to the Outlook option I posted earlier you could try the following formula in Column B of your spreadsheet:
=IF(ISNA(VLOOKUP(A19,Data!B:D,3)),"",VLOOKUP(A19,D ata!B:D,3)) Hope this helps. OTPM Not sure why the graphics are displaying above, however where you have the "face" graphic you should enter "B" followed by a colon followed by "D". |
#4
|
||||
|
||||
Quote:
When you write your reply, tick the "Disable smilies in Text" checkbox in the Miscellaneous Options section. |
#5
|
|||
|
|||
I've tried the function you gave, and it will put a name in the cell from the data tab, but I'm not sure what criteria it is using. It says that it is looking at A19, but the name it returns doesn't relate to that time.
for ex this is what I get: 0800-1005AMJohn1015-1220PMTim0120-0325PM John doesn't have a 0800-10005AM class, not sure why it is returning his name. The time is what the formula says it is looking up. I tried using this as my if true result, but I am just not having any luck getting getting any functions to look at my data for more than one criteria. I can have it return true If the room number in Data matches the room number specified on schedule, but I can't get it to return true if the room numbers match and the time matches. grrr! It seemed like it should be such an easy thing to accomplish. I do it all the time by eyeball! Thanks, Erin |
#6
|
|||
|
|||
Hi Erin
Is it possible to past a copy of your spreadhseet here so we can try and help you. OTPM |
#7
|
|||
|
|||
This is a more detailed sample of the spreadsheet that I am working from. The Data is on the Data tab and the schedule I want to create is on the Fall 2011 tab.
|
#8
|
|||
|
|||
Quote:
I have had a play with your spreadsheet however I have been unable to find a solution. I am sure there is a formula that will do it and hopefully someone with more detailed skills than I will be able to help. I know you can do it with VBA but it will need a VBA expert to help. Good luck. Tony |
#9
|
|||
|
|||
Thanks for trying Tony!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Variable fields? | Emalee77 | PowerPoint | 0 | 01-30-2011 05:58 PM |
Creating Lookup in Excel | vikash kumar | Excel | 2 | 12-06-2010 06:50 AM |
Variable Table_Array in VLOOKUP | pdab09 | Excel | 3 | 11-23-2010 05:28 AM |
Inputting variable data | axy | Word | 0 | 09-08-2009 04:50 PM |
Using the LOOKUP Command | Grapejuice | Excel | 2 | 10-15-2008 02:02 PM |