Automatically generate a 'live' appointment list from a diary spreadsheet

[steve80s]

Hi All, I wonder if anyone can help me with this query please.

I have a spreadsheet that we use as a diary for room bookings. I call this my Master Diary. It has a column for each room, and rows for each timeslot. The information is set to automatically pull from each one of our services individual diaries, eg. SLT Diary, CAMHS Diary, etc. I set this up using simple ='[Diary - SLT.xlsx]Thur 20-02-25'!I67 formulas. My Master Diary is often updated during to course of the day as appointments are added or removed from the services diaries. This is all working fine, I'm just explaining so you know how it works.

What I now want to do is create a separate spreadsheet that can pull the appointments from the Master Diary and display them as a simple list ordered by start time. I have created a dummy version of what I would like it to look like. Hopefully by looking at the image you will see what I am trying to achieve. The purpose of this is the Master Diary sheet for each day is quite large and clunky for our receptionists to work with. I am hoping I can create a more user friendly sheet that the receptionists can keep open and see a 'live list' of who is due to attend, and they can then indicate whether they arrive or not.

For info, where you can see a slot marked 'As above', I want the appointment list to ignore these. The reason we use 'As above' is to indicate the appointment is expected to continue beyond the original 30 minute time slot.

I'm really hoping someone knows a way of doing this! Thanks in advance.

 

[steve80s]

Hi All.

No responses received, so maybe I'm not being specific enough with what I'm asking (or maybe I'm asking the impossible).

Let me try explaining a different way. I want a way to check SheetA to see if the following cells contain a value (C5, C9, C13, C17, C21, C25, C29, C33, C37, C41, C45, C49, C53, C57, C61, C65, and then the same in columns F, I, L, O, R, T, X, AA, AD, AG, AJ, AM, AP, AS, AV, AY, BB, BE, BH).

I then want these values to appear in a vertical list on a separate spreadsheet (Sheet2) starting in B2.

Then on this new list I want C2 to contain whatever is one row DOWN from where it pulled B2's value from, D2 contains the value from 2 rows down, and E2 contains the value from 3 rows down.

A2 needs to display the value of whatever was in Column A of the same row , and F2 needs to contain the value from Row 2 of the original column.

Anyone know how to do this please?