Formula for selecting value in cell using multiple criteria?

Hi there,

The situation:

I have a large amount of data regarding room activities on a university campus. The variables are: Day (Mon-Fri), Time (09:00-16:00), Room and Duration (1-9 hours). In worksheet 1, these variables are positioned in columns with each row representing the different activities taking place.

I have tried using logic functions but I cant get it to work. All I want to do is import the duration of events from the first worksheet into that of the second given the selected room, date and time. So, if the room is Room001, the date dd/mm/yyyy, and the time 09:00 then the duration of the event equals x=1 to 9. The answer must be a formula as I have a great deal of info to sort through.

I hope this makes sense. Thank you in advance for any suggestions.

Regards,
PC

Hi PC,

welcome to the forum.

Please upload a sample workbook as this will save time recreating your data .... thx


Regards,
DILIPandey
<click on below * if this helps>

okay.. do you need 4 in D3 of "Duration summary" sheet ?

if so, then how and where your are considering date here ?



Regards,
DILIPandey
<click on below * if this helps>

Okay.. so try using below formula which need to be entered using key combination ctrl shift enter.

Formula:

Please Login or Register  to view this content.

see attached:- Example(19).xls


Regards,
DILIPandey
<click on below * if this helps>

Hi again,

Thank you for replying but my problem still remains unsolved. I can not seem to drag and copy the equation to fill all the boxes.
Unfortunately as the data within the spread sheet I am going to be applying the formula to is very large (the example I gave you contains 1% of the rooms, 0.001% of activities) I need an equation that I can simply copy.
Also your equation does not have room as a variable. The formula should contain 3 variables: Room, Time and Day.
Please see attached for what the 'Duration Summary' s/s should look like.

Thanks for your help!

PC

(Please see Example (21) for what happens when I copy the formula supplied)

Hi PC, now I included 3 variables in the below formula:-

Formula:

Please Login or Register  to view this content.

see attached:- Example(20).xls

note:-
1) fill up the blanks like I did in left columns
2) you have spelling mis-match for days in both the sheets e.g., thu vs thurs etc


Regards,
DILIPandey
<click on below * if this helps>

Hi PC, I have used Index function and Match function here along with Iferror error handler... suggest you to see excel in-built help to learn these function..

For filling up blanks.. I have selected left column's data and then pressed F5-> selected blanks->Enter. Now = sign and up arrow and pressed ctrl + enter.

Hope this helps.


Regards,
DILIPandey
<click on below * if this helps>