Help on Layout of Spreadhsheet

[Sharr76]

Hi all,

I am looking at creating an excel spreadsheet for Occupational Health surveillance information for employees, and I am really unsure of the best way to tackle this.

I will have employee, job title, type of health surveillance, date of surveillance, comments and next due date

What would be the best way of showing this information in excel as an employee could potentially have a number of different types of health surveillance, , eye test, hearing test, lung function tests etc

[FDibbins]

Great question, I will start to answer this by saying that the VERY 1st thing you need to do is design how teh data will be entered, and NOT to worry about whatthe output needs to look like. We see it far too often on the forum where members have atempted to 1st design what the output will look like, and then tried to build the input around that.

This is (pretty much) without exception the wrong way to do this. 1st figure out what inputs you will have, and structure a basic table that will capture this data. Excel works best with straight-forward 2-dimensional tables, generally with headings across the top, and dates down the side. If you structure it this way, excel formulas and functions will generally have a pretty easy task extracting the dats in almost any way you want

Once you have that, and can provide a few sample inputs, we can start to work on what your outputs need to look like.

[Bobsone]

Hi.

I am not an Excel expert but working from my limited experience here are some issues (errors which I made) that made my task more complicated.

Construct a background sheet which contains all the data fields and data, totals etc etc that you need in your sheet.
Avoid using merged cells.
Set your data fields in a logical format.
If at all possible keep all column headings to 1 cell per column
Try to avoid empty rows/columns. While this helps the look of the sheet it can create issues should you want to manipulate the data.

Then construct a type of interface sheet to display the data you want and in the way you want it. This draws its information from the background sheet and can have whatever formatting is needed/looks good, be self populating etc etc.

Regards

[Sharr76]

Hi all,

this is what I have in mind but in principle I am not sure who it will work within excel:

TOP ROWS
Surname – First name – Job Title – Shift – Start Date – PERM – Fit for Emp – Fit for Nights – Known medical conditions

Health Surveillance Categories

 Lung Function
 Skin Check
 Hearing Test
 Blood Pressure
 Urinalysis
 Drug Test
 DSE
 Eye Test
 Counselling, Help and Support
 Well Being Clinic

Then dates employee has undergone any of the above health surveillance checks

And a Comment column to add information into depending on the results of the health surveillance check

So a single employee potentially could undergo all of the above checks

[Bobsone]

HI

Here is something to look at
Working from the assumption that the data will be organized around each persons last name and that it is likely there will be more than one entry per person, i.e. regular testing/assessments.
The Display sheet indicates what kind of output may be useful, but there are a lot of options available (including using the Data sheet for pivot tables).
The Lists page contains each persons relevant bio data.
The Data page contains all data fields, starting with last name.

The Lists page data auto-fills the relevant cells on the Data page once a last name is entered Via drop down selection).
The Data page is organized; Last name, First name, Date, then the test/assessment criteria followed by the rest of the persons bio data.
The reasoning being, the persons bio data probably wont change a lot and is easily found on the lists page, where as the testing results will need to be manually entered for each entry.

[Sharr76]

Wow Bobsone, that looks great, thank you very much

So to add/show employees on the Display tab how would I do that?

[Bobsone]

So to add/show employees on the Display tab how would I do that?

Are you wanting more separate fields to display an individual persons data, or asking how to change the person in the field?

I originally set the display page as an example, copying to make new fields is a bit fiddly... perhaps an Excel Guru can make some suggestions
I have placed some more fields on the Display page, also on the data page I have selected the Sort function (Home>Sort and Filter), now there is a select box at the top of each column, these can organize the data order...

This layout is an example and may not serve as intended, e.g. the "Fit for work" and "Fit for nights" columns on the Lists page may be better positioned with the test columns on the Data page because the per-person values will probably change depending on their test outcomes.

Regards.

[Sharr76]

I have just realised that you can select an employee by the drop down list on the Display page, so that is exactly what I want to see.

[Bobsone]

Thats good to hear, I not sure I would be able to find a quick way to duplicate the Display page tables, still lots to learn .

I have been playing with the workbook some more and have changed the layout a bit and added some conditional formatting for The Fit for Emp-Nights cells (it is all good practice for me).

Regards.

[Sharr76]

Bobsone, that is fantastic thanks very much, I will not start to use this.

[Sharr76]

Hi Bobsone,

just one question, in the list tab, if the date field is left blank the Data tab shows the date in this format: 00/01/1900, how can I change this?