Cell Returns Value If Another Cell Fits Within Date Range?

My company hires out equipment and we need to keep track of where a piece of equipment was at a given time and who to charge the equipment to for a particular week or month.

Each piece of equipment will have a separate sheet, which will list all of the hire contracts which have been placed on that machine:

Column B: Hirer name
Column C: Contract start date
Column D: Contract expiry date

Then there will be a main sheet (Summary) which will have all equipment listed, and all of the chargeable dates listed (see example below)

Week 1 ||
Week Commencing (B6) |Week Ending (C6) ||
25/11/2013 (B7) | 1/12/2013 (C7) ||

I want to add a Box underneath each piece of equipment on the main sheet that will show who to charge to. For example, underneath Week 1, I want it to show who the hirer is based on whether the Week Commencing to Week Ending dates fit within the contract start and expiry dates.

I have come up with a formula that almost works, but I can only get it to refer to a single cell in the equipment sheet. As soon as I try to get it to refer to all columns, it just automatically comes up with 'not on hire'.

This is my formula: =IF(AND(('Equipment1'!C14<='Summary'!C7),('Equipment1'!D14>='Summary'!B7)),('Equipment'!B14),"Not on hire")