Populating Calendar From Call Log

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
  1. 2010
Hi all.

I'm creating a call log for those staff who are on flexible furlough so we can monitor which days everyone is working and also have a centralised record of the contact.

So the call log is something like this (tab is named 'Call log'):
call log.JPG


The tricky bit, however is populating the calendar - here's an extract of the calendar (tab is named 'Calendar':
calendar.JPG


So as you can see, I intend for us to input a date commencing and a date ending to give a range of dates that the employee is in the office. For that range I want each date that a person is in the office to have the word "In" in the relevant cell on the calendar. Other cells can remain blank. I've omitted weekends from the calendar.

That's difficult enough but please bear in mind that we could be bringing people back several times during the month. I considered using a VLOOKUP but that would only pick up the first result for each staff member. If we call them in several times during the month I need every day to be included.

I hope that makes sense and would be really grateful for any assistance.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,418
ok, so let me know if you will be able to use this attendance list with PQ add-in (some bugs) or (preferred) XL2016 version minimum :biggrin:
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
  1. 2010
So we can't use 2016+ because there will be various users inputting to this sheet at different times and we don't have later versions of excel until we upgrade to Office365.

Not sure what you mean by add-in....I can download PowerQuery on my machine but I will have trouble getting others to if it needs to be on all machines. I need to be able to set up a sheet that people can input into without having to do anything else. Anything that can be incorporated into the file though such as VBA will be fine.

Thanks for your time.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,418

ADVERTISEMENT

as I said before: PQ add-in allows you to use Power Query with XL2010/2013
in your case will be better to wait for XL365
hope it won't take a long ;)
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
  1. 2010
Well I need a solution by tomorrow so I'll have to figure out another way. I'll come up with something :unsure:
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
  1. 2010
So this actually wasn't difficult at all. I was overthinking it. Thought I would update the thread for completeness in case anyone stumbles across it in the future. No need for PowerQuery and easy enough to do in Excel 2010.

I simply put a calendar next to the call log and for each day and row determined whether the date was in the range. For example:
=IF(AND(H$3>=$B4,H$3<=$C4),"In","")
Where H3 is the header with the date in it, B4 is date commencing and C4 is date ending.

This works for each row but there could be multiple rows for each staff member and I needed to consolidate them into one row on a calendar.
So I created a calendar on another tab with each staff member and referred back to the calendar I have just created.
=IF(COUNTIFS('Call log'!$A$4:$A$250,Calendar!$A3,'Call log'!H$4:H$250,"In")>0,"In","")
Using countifs I could check for both the staff name (A3) and whether they are "In" for any given date.

I then hid the calendar on the call log as there is no need for people to see it.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,809
Messages
5,574,435
Members
412,592
Latest member
moonsugar
Top