Thanks for answering. I have created a calendar for reservations (e.g. hotel calendar)
One sheet is the calendar and another sheet has the reservations (arrival and departure dates)
On the reservations sheet I manually fill the arrival and departure dates and with the IF formula autofill the calendar with the names of the guests.
The problem is that on the calendar i want between different reservations different colours. So i want to write the name of the guest with a colour background and it autofills with name and colour.
I do not know many about excel , nothing about VBA and programming but i have a lot of patience.
Explain here with the formula that i wrote :
=IF(AND(F6>=Sheet2!$B$9,F6<Sheet2!$B$10),Sheet2!$B $3,IF(AND(F6>=Sheet2!$C$9,F6<Sheet2!$C$10),Sheet2! $C$3,IF(AND(F6>=Sheet2!$D$9,F6<Sheet2!$D$10),Sheet 2!$D$3,IF(AND(F6>=Sheet2!$E$9,F6<Sheet2!$E$10),She et2!$E$3,IF(AND(F6>=Sheet2!$F$9,F6<Sheet2!$F$10),S heet2!$F$3,IF(AND(F6>=Sheet2!$G$9,F6<Sheet2!$G$10) ,Sheet2!$G$3,IF(AND(F6>=Sheet2!$H$9,F6<Sheet2!$H$1 0),Sheet2!$H$3,IF(AND(F6>=Sheet2!$I$9,F6<Sheet2!$I $10),Sheet2!$I$3,IF(AND(F6>=Sheet2!$J$9,F6<Sheet2! $J$10),Sheet2!$J$3,IF(AND(F6>=Sheet2!$K$9,F6<Sheet 2!$K$10),Sheet2!$K$3,IF(AND(F6>=Sheet2!$L$9,F6<She et2!$L$10),Sheet2!$L$3,IF(AND(F6>=Sheet2!$M$9,F6<S heet2!$M$10),Sheet2!$M$3,IF(AND(F6>=Sheet2!$N$9,F6 <Sheet2!$N$10),Sheet2!$N$3,IF(AND(F6>=Sheet2!$O$9, F6<Sheet2!$O$10),Sheet2!$O$3,IF(AND(F6>=Sheet2!$P$ 9,F6<Sheet2!$P$10),Sheet2!$P$3,IF(AND(F6>=Sheet2!$ Q$9,F6<Sheet2!$Q$10),Sheet2!$Q$3,IF(AND(F6>=Sheet2 !$R$9,F6<Sheet2!$R$10),Sheet2!$R$3,IF(AND(F6>=Shee t2!$S$9,F6<Sheet2!$S$10),Sheet2!$S$3,IF(AND(F6>=Sh eet2!$T$9,F6<Sheet2!$T$10),Sheet2!$T$3,IF(AND(F6>= Sheet2!$U$9,F6<Sheet2!$U$10),Sheet2!$U$3,IF(AND(F6 >=Sheet2!$V$9,F6<Sheet2!$V$10),Sheet2!$V$3,IF(AND( F6>=Sheet2!$W$9,F6<Sheet2!$W$10),Sheet2!$W$3,IF(AN D(F6>=Sheet2!$X$9,F6<Sheet2!$X$10),Sheet2!$X$3,IF( AND(F6>=Sheet2!$Y$9,F6<Sheet2!$Y$10),Sheet2!$Y$3,I F(AND(F6>=Sheet2!$Z$9,F6<Sheet2!$Z$10),Sheet2!$Z$3 ,IF(AND(F6>=Sheet2!$AA$9,F6<Sheet2!$AA$10),Sheet2! $AA$3,IF(AND(F6>=Sheet2!$AB$9,F6<Sheet2!$AB$10),Sh eet2!$AB$3,IF(AND(F6>=Sheet2!$AC$9,F6<Sheet2!$AC$1 0),Sheet2!$AC$3,IF(AND(F6>=Sheet2!$AD$9,F6<Sheet2! $AD$10),Sheet2!$AD$3,IF(AND(F6>=Sheet2!$AE$9,F6<Sh eet2!$AE$10),Sheet2!$AE$3,IF(AND(F6>=Sheet2!$AF$9, F6<Sheet2!$AF$10),Sheet2!$AF$3,IF(AND(F6>=Sheet2!$ AG$9,F6<Sheet2!$AG$10),Sheet2!$AG$3,IF(AND(F6>=She et2!$AH$9,F6<Sheet2!$AH$10),Sheet2!$AH$3,IF(AND(F6 >=Sheet2!$AI$9,F6<Sheet2!$AI$10),Sheet2!$AI$3,IF(A ND(F6>=Sheet2!$AJ$9,F6<Sheet2!$AJ$10),Sheet2!$AJ$3 ,IF(AND(F6>=Sheet2!$AK$9,F6<Sheet2!$AK$10),Sheet2! $AK$3,IF(AND(F6>=Sheet2!$AL$9,F6<Sheet2!$AL$10),Sh eet2!$AL$3,IF(AND(F6>=Sheet2!$AM$9,F6<Sheet2!$AM$1 0),Sheet2!$AM$3,IF(AND(F6>=Sheet2!$AN$9,F6<Sheet2! $AN$10),Sheet2!$AN$3,IF(AND(F6>=Sheet2!$AO$9,F6<Sh eet2!$AO$10),Sheet2!$AO$3,IF(AND(F6>=Sheet2!$AP$9, F6<Sheet2!$AP$10),Sheet2!$AP$3,IF(AND(F6>=Sheet2!$ AQ$9,F6<Sheet2!$AQ$10),Sheet2!$AQ$3,IF(AND(F6>=She et2!$AR$9,F6<Sheet2!$AR$10),Sheet2!$AR$3,IF(AND(F6 >=Sheet2!$AS$9,F6<Sheet2!$AS$10),Sheet2!$AS$3,IF(A ND(F6>=Sheet2!$AT$9,F6<Sheet2!$AT$10),Sheet2!$AT$3 ,IF(AND(F6>=Sheet2!$AU$9,F6<Sheet2!$AU$10),Sheet2! $AU$3,IF(AND(F6>=Sheet2!$AV$9,F6<Sheet2!$AV$10),Sh eet2!$AV$3,IF(AND(F6>=Sheet2!$AW$9,F6<Sheet2!$AW$1 0),Sheet2!$AW$3,IF(AND(F6>=Sheet2!$AX$9,F6<Sheet2! $AX$10),Sheet2!$AX$3,IF(AND(F6>=Sheet2!$AY$9,F6<Sh eet2!$AY$10),Sheet2!$AY$3,IF(AND(F6>=Sheet2!$AZ$9, F6<Sheet2!$AZ$10),Sheet2!$AZ$3,"ΚΕΝΟ"))))))))))))) ))))))))))))))))))))))))))))))))))))))