How do I compare two dBs(spreadsheets) based on multiple criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, so here's the rundown...

2 seperate databases (and by db's I mean spreadsheets)... one dB has the
information for the start time/end time/date/location of medical
staff(nationwide). The second dB is a nation wide index of all of the
accidents that lists time/location/date and other remarks. What I am looking
to do is make 1 cell(on the accident dB) that references the medical staff dB
and tells me if the accident occured between the hours that a medical staffer
was on duty. A simple Yes or No is all that I'm looking for, but it's gotta
match the date, city, and reference between the work hours. The staff dB is
expected to be around 3000 lines and the accident db around 5000. Please Help
me!!!
 
Assume Sheet1 (Medical Staff) has the following:

Col A : Start time
Col B : End time
Col C : Date
Col D : Location

Sheet2 (Acccidents):

col A : Accident Time
Col B : Location
Col C : Date

In Col D (Sheet2 - and assuming data starts in row 2) put:

=IF(SUMPRODUCT(--(Sheet1!D2:D20=B2),--(Sheet1!C2:C20=C2),--(A2>=Sheet1!A2:A20),--(A2<=Sheet1!B2:B20))>0,"Y","N")

and copy down.

Obviously adjust ranges to suit your requirement.

HTH
 
Toppers... you rock! Thank your very much. I would have never guessed
SUMPRODUCT.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I use the SUMIF function using multiple criteria 1
Construct staff pay timesheet 1
How do I retrieve data based on multiple search criteria? 1
how do I count a rota with no dates and using 24 hours clock 2
How do I average every 3rd cell in a column of a spreadsheet? 3
How do I count frequency based on 2 criteria (including month) 18
How do i enter minus hours in a spreadsheet? 3
How do i input multiple dates in one Access Field 5

Back
Top