How to create a new spreadsheet or amend an existing one

Hi all,

Looking for some collective wisdom / advice before I start tampering with, or creating a spreadsheet.

I've just been handed the wonderful task of maintaining a spreadsheet which, in effect, records details of what days contracted employees work, on what 'exercise' they worked on, and the 'activity' they worked upon within the exercise.

I cannot post a copy as commercially sensitive, but the sheet itself is fairly standard affair. Dates for the year progress by row top to bottom, with employee names by column left to right. There are then various cell entries for the exercise and then the activity each undertake. This information is firstly input when they are allocated the work, and then updated when the complete it, this is currently achieved by changing the cell contents from x to 1.

I've today learned this information is then used to manually complete a further spreadsheet, which is then sent to payroll to pay contracted employees for the work completed.

The current set up involves the 'personal details' such as name, payroll ref on one spreadsheet, with the booking details mentioned in paragraph 2 on another. A combination of these is then manually entered into a third spreadsheet.

This process seems 'wonderfully longwinded' and I immediately thought there must be a way to automate, so that one sheet could cross reference the other and do all the leg work. Trouble is I'm not sure how to do it, so looking for pointers any kind souls on here might offer.

I've got a fairly basic knowledge handle on formulas etc, and happy to google things like that, but in terms of set up I guess some of you will have experience of this type of thing??

Any help gratefully received.


Ballst

Thanks davesexcel.

For ease I have attached the one spreadsheet, and each of the tabs in the current set up represents a separate spreadsheet. Below is a hopefully sensible explanation.

Tab one – Diary

From right to left this shows each person, and top to bottom each working day of the year. The person section is then broken down to 5 areas, Exercise, Activity, Task 1, Task 2, Paid. Each exercise is numbered, and there are a six activities which can occur within an exercise, description in the activity key.

Some people can only take part in Task 1, whilst others can do either Task 1 or 2. This is currently signified by the Green highlight in the relevant cells. If the person is selected to be used for an exercise, the relevant cells are completed, with the exercise number and activity type recorded. Until the task is completed an x is entered against the Task. Once paid this is manually changed to a 1 and the cells are manually changed to red text to visually show payment.

Tab two – Employee Detail

A basic spreadsheet holding employee details.

Tab three – Authorisation Tab

The information contained in this sheet is currently manually entered on a weekly basis, as per the example entries. The majority of the information comes from the diary spreadsheet, with the pay ref information coming from the Employee Detail spreadsheet. Once all the information is entered the spreadsheet is then circulated for authorisation, before it is sent to payroll.

As mentioned in my original post there seem to be a number of areas this process can either be improved or automated, in particular the Authorisation sheet. My initial though is, could this be populated using a look up of some description? It would be useful if the Business Activity information could somehow populate, perhaps running off the activity code information? Also if when entering the person’s surname or employee id the other column could populate.

Again, if anyone is able to assist, I would be very grateful.


Ballst

Sorry for the tardy reply, I'm on leave but seeing your reply wanted to try it out, so have quickly logged on. Formula works well, and is very useful. I guess by substituting the column references this could 'look up' information from other spreadsheets, as the information in the example spreadsheet is normally held on different spreadsheets?

Also, is there a formula / method of populating Column J in the authorisation tab with the booking information in the Diary Tab? Appreciate that might mean changing the diary tab a bit!!

Hi JeteMc,

Back in the office, so thank you for the reply, and the working attachment. I think I've got my head around the magic you've performed with the formulas.

The problem with your proposed set up is that all of the information I end up working with comes from the diary sheet. So for example, in January I might book something in for w/c 23 July, this would be entered into the diary sheet. It's only when we reach the w/e 27 July that an authorisation sheet is produced for that particular week. I cannot see anyway around the diary sheet not being the area this data is originally entered, unless I'm being daft, if so my apologies.

As I say very grateful for your assistance, if I were to work on another example and re-post would you be kind enough to give it the once over?