creating a calendar with auto-update functions

**cdyason** · 02-15-2011, 12:07 PM

Hi,
I have been tasked with creating an excel ‘tool’ to manage the HR of a company. There are many people on many shifts each day, and it is becoming hard to keep track of where people are when.
I have created the layout of the workbook, as I desire it, but am stuck on getting the formulas right, having only just started experimenting with various Excel formulas (IF,LOOKUP etc) and have never used macros or VBA.
Basically I wish to automatically update cells E2 and E3 on tab ‘Joe Bloggs’ based on the text in today’s date cell in the calendar on that same tab. The index pages will then be updated. The text in the calendar cell will simply display the employee’s location.
The whole workbook will be updated by several different users in various locations, and will be located on a server for this purpose.
I have read various other posts regarding calendars/auto-update, but none seem to be the right fit.
Any help would be hugely appreciated. Also, please let me know if I am going about this the wrong way.
Cheers.

**6StringJazzer** · 02-15-2011, 01:17 PM

See attached for solution.

If you are new at Excel, the formula may not be obvious. You have set up a grid with months as the columns and days as the rows, so you need to figure out how to select a cell in the grid. To get the column, use the month number from the date (MONTH function). To get the row, use the day number (DAY function).

Then you use INDEX, which allows you to specify a range, and a row and column number within that range.

You may want to check Excel Help for more details on those functions.

**MarvinP** · 02-15-2011, 01:23 PM

Hi cdayason and welcome to the forum,

Find the attached with some data I've created to try to solve your problem. Pivot Tables are weak as they only put numbers in the summation area, and it looks like you want locations. Advanced Filters keep the location string but need to be sorted. and don't put months across the columns.

I think it is a good idea to try some examples with data before having a final data structure.

Also, I'm somewhat concerned when you say "updated by several different users on a server" as if two people try to update at the same time your system may fail. Access has record locking but I don't think it is built into Excel (I may be wrong on this).

Look at the attached and see what it does and doesn't do for your problem.

**cdyason** · 02-16-2011, 08:25 AM

Thanks heaps both of you!

6 String Jazzer - simple solution and what I was looking for = great!! Cheers

MarvinP - yes I'm playing around with the thing the whole time to get structure right. There's generally only one person editing the sheet at a time but others have access to it in order to see where everyone is. If I have problems I'll move to another tool anyhow. Thanks!

There'll be more to the sheet eventually but this will get me going in the mean time..