Thread: Nesting the IF, AND and SUMPRODUCT functions across worksheets

I'm really new to excel, but was tasked with creating a reservation sheet for our Aquatics Community Center. It has 3 work sheets where one is supposed to be able to enter basic information about reservations for different rooms. On the last sheet is a visual table of the date, the room and the hours and this is supposedly to show the the availability of the rooms and is supposed to fill it self out automatically, based on the information you enter into the first three worksheets.

Please look at my worksheet (BOOK 1) and the template (COPY OF HOTEL RESERVATIONS) that I am basing it on to get a better picture.

I tried to create a nested function that would fill a cell with the values of 0,1 or -1 depending on whether certain variables were true or false. I was then intending to format the cells conditionally to turn a different color based on the number.

This is the code that I used:

= =IF(AND(ConferenceRooms!$D$2:$D$1001='Schedule&Availabilites'!B$5, ConferenceRooms!$G$2:$G$1001='Schedule&Availabilites'!B$6), 1-SUMPRODUCT((ConferenceRooms!$E$2:$E$1001<='Schedule&Availabilites'!A7)*(ConferenceRooms!$F$2:$F$1001>='Schedule&Availabilites'!A7+1)),"0")

The AND function is supposed to relate to the room type and the date of the booking. The SUMPRODUCT.... well to be honest I'm not really sure. I tried to modify the code that was used in the template I looked at and I researched it as much as possible, But I'm still struggling to understand.

If someone could please try and help me out, I would really appreciate it.

I'm also not sure If I've posted all the information need, so if anything else is require, please just let me know.
Thanks

Thank you bebo021999, I see the changes that you've made and they mostly make sense to me. There is one problem though, in terms of the way you linked the booking date to the schedule. You linked it to cell A3, but that cell is just a reference cell for the actual interactive calendar and the plan was for it to be hidden once the workbook is complete.

What I want is for the booking dates to actually be linked to the interactive calendar dates. So each time there is a new date in the cell, the schedule will change accordingly. The way you've set it up right now, it only works with the ONE date that is in A3.

Subsequently, I tried changing the linked cell to B$5 and while it worked for the first column (Salon A), when I dragged it across to the Salon B, which is supposed to be under the same date, the cell changed to C$5 and subsequently didn't work.

1-SUMPRODUCT((ConferenceRooms!$D$2:$D$9='Schedule&Availabilites'!B$5)*(ConferenceRooms!$G$2:$G$9='Schedule&Availabilites'!B$6)*(ConferenceRooms!$E$2:$E$9<='Schedule&Availabilites'!$A7)*(ConferenceRooms!$F$2:$F$9>='Schedule&Availabilites'!$A7))

I've attached the workbook that I made the changes to so that you can see more clearly what I've done.

Book1.xls

To me this shouldn't be happening because if cells are merged then either they should all hold the same information or the formula should automatically skip to the next set of merged cells and ignore the the individual cells that compose a merged cell.

However that is clearly not the case..... is there any other way then that I can format my table so that I get my desired results?

Thank you so much by the way!

Try this,

=(SUMPRODUCT(--(ConferenceRooms!$D$2:$D$100=LOOKUP(9E300,$B$5:B$5)),--(ConferenceRooms!$G$2:$G$100=B$6),--(ConferenceRooms!$F$2:$F$100>=$A7),--(ConferenceRooms!$E$2:$E$100<=$A7+"00:29"))=0)+0

Copy across & down

If you are on XL2007 or later replace SUMPRODUCT with COUNTIFS, which is faster than SUMPRODUT.