Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-14-2011, 08:49 AM
ebolton ebolton is offline Multi-Variable Lookup help Windows 7 Multi-Variable Lookup help Office 2007
Novice
Multi-Variable Lookup help
 
Join Date: May 2010
Posts: 7
ebolton is on a distinguished road
Default 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!
Attached Files
File Type: xlsx schedulesample.xlsx (10.7 KB, 19 views)
Reply With Quote
  #2  
Old 05-03-2011, 05:23 AM
OTPM OTPM is offline Multi-Variable Lookup help Windows 7 32bit Multi-Variable Lookup help Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 05-03-2011, 05:45 AM
OTPM OTPM is offline Multi-Variable Lookup help Windows 7 32bit Multi-Variable Lookup help Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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".
Reply With Quote
  #4  
Old 05-03-2011, 06:50 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Multi-Variable Lookup help Windows 7 32bit Multi-Variable Lookup help Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Quote:
Originally Posted by OTPM View Post
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".
:D represents the smiley face you can see in your post.

When you write your reply, tick the "Disable smilies in Text" checkbox in the Miscellaneous Options section.
Reply With Quote
  #5  
Old 05-03-2011, 09:23 AM
ebolton ebolton is offline Multi-Variable Lookup help Windows 7 Multi-Variable Lookup help Office 2007
Novice
Multi-Variable Lookup help
 
Join Date: May 2010
Posts: 7
ebolton is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 05-04-2011, 08:33 AM
OTPM OTPM is offline Multi-Variable Lookup help Windows 7 32bit Multi-Variable Lookup help Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Erin
Is it possible to past a copy of your spreadhseet here so we can try and help you.
OTPM
Reply With Quote
  #7  
Old 05-04-2011, 09:35 AM
ebolton ebolton is offline Multi-Variable Lookup help Windows 7 Multi-Variable Lookup help Office 2007
Novice
Multi-Variable Lookup help
 
Join Date: May 2010
Posts: 7
ebolton is on a distinguished road
Default

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.
Attached Files
File Type: xlsx schedulesample.xlsx (20.4 KB, 13 views)
Reply With Quote
  #8  
Old 05-05-2011, 03:13 AM
OTPM OTPM is offline Multi-Variable Lookup help Windows 7 32bit Multi-Variable Lookup help Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by ebolton View Post
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.
Hi Erin
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
Reply With Quote
  #9  
Old 05-05-2011, 05:28 AM
ebolton ebolton is offline Multi-Variable Lookup help Windows 7 Multi-Variable Lookup help Office 2007
Novice
Multi-Variable Lookup help
 
Join Date: May 2010
Posts: 7
ebolton is on a distinguished road
Default

Thanks for trying Tony!
Reply With Quote
Reply

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
Multi-Variable Lookup help 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
Multi-Variable Lookup help Using the LOOKUP Command Grapejuice Excel 2 10-15-2008 02:02 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft