Need help to simplify my formulas since the calculation get slow.

Hi there,
Im newbie so please understand,

I have this formula on sheet Room Visual

=IF(AND($A6<>"",$A6<>""),1-SUMPRODUCT((CheckIn<=C$5)*(CheckOut>=C$5+1)*((RoomResN=$A6))),"")

value of CheckIn = =OFFSET(Reservation!$D$5:$D$1000,0,0,COUNTA(Reservation!$D$5:$D$1000),1)

value of CheckOut = =OFFSET(Reservation!$E$5:$E$1000,0,0,COUNTA(Reservation!$E$5:$E$1000),1)

value of RoomResN =
=OFFSET(Reservation!$N$5,0,0,MAX(COUNTA(Reservation!$N:$N),COUNTA(Reservation!$O:$O),COUNTA(Reservation!$P:$P),COUNTA(Reservation!$Q:$Q),COUNTA(Reservation!$R:$R),COUNTA(Reservation!$S:$S),COUNTA(Reservation!$T:$T),COUNTA(Reservation!$U:$U),COUNTA(Reservation!$V:$V),COUNTA(Reservation!$W:$W))-1,10)

This formula makes the excel computation really slow. Anyway I can simplify the formula? Thank You.

Document Attached (Macro Enabled)

-AliGW
I tried to limit the rows like Reservation!$N1:$N1000 but im getting error on Room Visual Sheet.

-Glenn Kennedy
I've read about using INDEX instead of OFFSET but I really dont know how to convert specially the value of RoomResN (Range Name).

I only need rows up to 2000 for 1 year reservations... I just dont know how to limit the formulas...

Hi Ali,

Sorry the attachment was not updated, the value of NewResN is:

=OFFSET(Reservation!$N$5,0,0,MAX(COUNTA(Reservation!$N:$N),COUNTA(Reservation!$O:$O),COUNTA(Reservation!$P:$P),COUNTA(Reservation!$Q:$Q),COUNTA(Reservation!$R:$R),COUNTA(Reservation!$S:$S),COUNTA(Reservation!$T:$T),COUNTA(Reservation!$U:$U),COUNTA(Reservation!$V:$V),COUNTA(Reservation!$W:$W))-1,10)

That would remove the errors but computation is slow.

Originally Posted by tim201110

i'd use smart table

BTW, there is no xlsb in 2003

Thanks Tim, I will check it out.

The formula aims to have a result of 1, 0 and -1. 1 for room is occupied, 0 for vacant and -1 for double reservation. When a reservation was added on the first sheet, the formula will automatically add the dates and duration on the next sheet. The formula will match the corresponding date and room number from sheet2 to sheet 1.

Thank you for all your help, I was able to reduce calculation from 5 seconds to 2 seconds which is considerable. The problem really lies on the value of the Range RoomResN.

Thanks to AligW for reminding to limit the ranges so I change it to:

=OFFSET(Reservation!$N$5,0,0,MAX(COUNTA(Reservation!$N$5:$N$1200),COUNTA(Reservation!$O$5:$O$1200),COUNTA(Reservation!$P$5:$P$1200),COUNTA(Reservation!$Q$5:$Q$1200),COUNTA(Reservation!$R$5:$R$1200)),5)

I also learned that when limiting the value so instead of $N:$N, I used $N$5:$N$1200, I should use absolute value both the row and the column or else the Name Range will change relative to the pointers location. I also reduce the number of coulumns form 10 to 5. Now my formula is ok.

Thanks also TIM for the help.

Sorry. Thank You!