Thread: Calendar Controlled by List

Hello, i've been struggling with this issue for a while now, so i decided to post it online.
What i have is a List, with date, time, names.. much like a calendar but not visually welcome.
I Need to write a new appointment in that list and it should appear in the right place at a calendar on sheet2.

So basically a inverted ÍNDEX, LOOKUP...

I don´t mind doing it on the same sheet, the list can be at first columns, and them the calendar comes!

I also got more than 1 people to fill. each have a number, so i tried to add the number at the end of each hour for different people, like "HourInNumberFormat+PeopleNumber", because the rows would be reapeated.

Sample will show it better, it is not that complicated to understand, but i tried everything i know.

Hello,

it's possible with a formula, but you need to make a few changes to the spreadsheet:

On the calendar sheet, insert a new column between C and D and enter only the start times in that new column
On the calendar sheet, enter the people number into column C, in the same format as it is in the Control sheet, i.e. Nr1, Nr2, etc.

Now you can use this formula in E6:

=IF(ISERROR(LOOKUP(2,1/((Control!$A$3:$A$500=Calendar!$C6)*((F$4+$D6>=(Control!$C$3:$C$500+Control!$D$3:$D$500))*(F$4+$D6<(Control!$C$3:$C$500+Control!$E$3:$E$500)))),Control!$B$3:$B$500)),"",LOOKUP(2,1/((Control!$A$3:$A$500=Calendar!$C6)*((F$4+$D6>=(Control!$C$3:$C$500+Control!$D$3:$D$500))*(F$4+$D6<(Control!$C$3:$C$500+Control!$E$3:$E$500)))),Control!$B$3:$B$500))

copy down and across. You can hide column C and D.

see attached.

cheers,