Help with worksheet formulas....Please?

[Jules]

Hi everyone! I need help with this formula, I tried an if formula, but it's
way past my ability and thought I might try the discussion group...any help
would be great.

WORK SHEET ONE

C2 Patient
D2 Surgeon
E2 Group
F2 empty
G2 day of week
H2 date of service
L2 OR Suite
M2 Time in room
N2 Time out
O2 total minutes
P2 total time
R2 service

WORK SHEET TWO

A2 GROUP
B2 OR
D2 DATE OF BLOCK
E2 DATE CODE
F2 WEEK OF MONTH
G2 DAY OF WEEK
H2 BLOCK START
I2 BLOCK END

What I need to do is determine if a surgery in worksheet one meets the
criteria of the surgeons block time...is it the right group, the right date,
the right day of week , is it equal to or after the start time and be for the
end time?

If the above is true, another thing I need but do not need to have in the
same formula, is it the right room number?

Any suggestions would be wonderful!

Thanks so much and have a great day.

 

[Dave F]

What's the IF formula you tried to use?

It's hard to understand what you're looking to do without a more concrete
explanation.

It would also help if you gave some of the data your columns refer to.

Dave

 

[Jules]

WORK SHEET ONE

C2 Patient
D2 Surgeon (name)
E2 Group (group surgeon belongs to)
F2 empty
G2 day of week (Mon, Tue, Wed...)
H2 date of service (date the surgery was performed)
L2 OR Suite (room surgery took place)
M2 Time in room (time surgery started)
N2 Time out (time surgery ended)
O2 total minutes (total surgery minutes)
P2 total time (total minutes plus turnover (room cleaning and setup))
R2 service (orthopedic, pain mtg, dental etc.,.)

WORK SHEET TWO

A2 GROUP (group surgeons belong to)
B2 OR (room reserved to do surgery)
D2 DATE OF BLOCK (actual date of reservation)
E2 DATE CODE (date in text code)
F2 WEEK OF MONTH (2nd tues, 1 and 3 wednesday....utilizing just numbers)
G2 DAY OF WEEK (day of reservation , mon , tues...)
H2 BLOCK START (time the surgeon has the room...starting at 0730)
I2 BLOCK END (time reservation has ended...time in 16:30)

The first worksheet consists of all the surgeries performed for a year....I
need to determine if the surgery was performed within the block (reservation)
time. Does this help?

I'm really bad at explaining what I need...

Thanks for your help.

 

[Dave Peterson]

If it's the same date, won't it be the right day of week?

Put this in S2 of Sheet1.

=isnumber(match(1,(e2=sheet2!a2:a999)*(H2=Sheet2!d2:d999)
*(m2>=sheet2!h2:h999)*(n2<=sheet2!i2:i999),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

And you can just keep adding more conditions inside that product area. Just
wrap the expression in ()'s.

False means it didn't find any row that matched all the criteria at the same
time. True means that it found a row that matched all that stuff.