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

steve80s

New Member
Joined
Aug 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Reception Appointment List.png
    Reception Appointment List.png
    21.4 KB · Views: 7
  • Example Diary Sheet.png
    Example Diary Sheet.png
    84.6 KB · Views: 6
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?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top