Forum Discussion
Automatically updating "Calendar" when new data is added?
I need a way to automatically update the "Calendar View" worksheet (see example) with each new entry I add to the "Room Assignments & Dates" worksheet. Additionally, the "Calendar View" should somehow flag/highlight when there's overlapping assignments, or, overbooked rooms. Presently, I have to manually add/edit each row in the "Calendar View".
Additional info: Most rooms will allow for two people, so maybe I'd need to create room numbers that reflect that, e.g. Fake Room 201 holds two people, so maybe the room numbers needs to be 201-1 and 201-2, showing that there's two beds in one room?...)
Hello aj1152,
VBA should do it, I've provided the file on the attachment, the code will reflect the person's checked in and out on the database into the Calendar View sheet, and handles overlaps by creating unique room identifiers and highlight assignments in light green and yellow if the same building and room number has two or more person accomodating it.
Provided an "Autopopulate" button to run the macro.
online sql：
create temp table aa as
select split_num(regexp2('^\d+',`Check-in`)||'-'||regexp2('^\d+',`Checkout`)) day,regexp2('\D+$',`Check-in`) surfix,* from RoomArrange limit 20;
cli_split_data~aa~,~day;
create temp table bb as
select Building,Room,day||surfix day2, group_concat(Person||`Student ID`) stu from aasplit group by Building,Room,day2 order by day2 COLLATE NATURAL_CMP;
//select * from bb;
cli_create_two_dim_no_order~bb~day2~stu;
select * from bb_two_dim;
Hello aj1152,
VBA should do it, I've provided the file on the attachment, the code will reflect the person's checked in and out on the database into the Calendar View sheet, and handles overlaps by creating unique room identifiers and highlight assignments in light green and yellow if the same building and room number has two or more person accomodating it.
Provided an "Autopopulate" button to run the macro.- This is great! Very much appreciated. I'm going to try to update the VBA file, tweaking it to make it work with my actual real-world data (e.g. I need to also include the occupants' last names) but this is very very helpful! If I get stuck, mind if I ask for some support?
- Yea sure, there's a lot to improve in the code itself anyway.
I forgot to mention, on 'Calendar View' sheet, on header Dates, it should be manually typed and must be in date format (since when I open your sample file it wasn't on date format.)
