Excel help - Drop down list/info?

Tommy Teapot

Prominent Member
Joined
Feb 12, 2006
Messages
2,779
Reaction score
487
Points
509
Location
N.ireland
Hi,

I’m trying to create a spread sheet using excel, its main purpose is to record dates when staff attended training. The left hand side is the list of staff and along the top has the different training courses. The main table contains dates when staff attended a training course (dd/mm/yy). The main purpose of this table is to record dates of when training has been attended over the years and when they’re next due again etc. At present I’m listing previous dates using the comments box within the cells, each time someone attends a training course I add that date to the comments box, I then change the date in the cell to indicate when they’re next due their training Ie if training was attended 1/1/16, the cell would read 1/1/17.

I was hoping to get some advice on laying this table out better, mainly another way of displaying the previous dates of training attended (the comments box doesn’t look great & could easily be deleted by mistake). Ideally I would like the cells to indicate the next due date, then, when you click the cell it would reveal a drop down list (non-selectable) of dates previously attended. I’m not quite sure if it can be done that way? I need something that’s easily updated & maintained so my colleague can update the table when I’m not here.

I appreciate any help.

Thanks!
 
Yes so what you have here is multi dimensional data that you are trying to put in a 2d sheet.

Can come up with some suggestions but as a minor curve ball have you got/considered Microsoft Access?
 
Thanks for the reply.

I havent used access before, im not sure how it works, but i'd be prepared to look into using it if it would make life easier. Is the excel route a bit unpractical with regards to what i want etc? This spreadsheet is stored on an intranet, which can easily be accessed via management to amend or view, so its handy in the sense most people are familair with excel and have no trouble amending or viewing the spread sheet.
 
As above, you have two entities (people and courses) and they have a many to many relationship. That's hard to model in Excel.

What about using each row to record a course attendance.

So you have:

Date Course Person

You then create a pivot table using the Max function of the date to see the last time each person took each course.
 
What you really want is to 'normalise' your data so each table is one-dimensional. So if sticking to Excel I would create three or more sheets:

1 - Employees - a list of employees with their details including importantly some kind of unique ID
2 - Courses - as above but one row per course, including an ID
3 - Courses attended - a three table column with employee ID, Course ID and date. If a given employee does a given course more than once, they will have more than one row here

Then in further sheets you can build reports by linking this data and querying. Do you have Excel 2013 or later? If so read/watch this: Create a relationship between tables in Excel - Office Support

What you are really doing here is making a basic database, which is what MS Access is, which lends itself more naturally to what you are doing than Excel.
 
Here's a sample Excel sheet using a pivot table to display a simple list of training courses

Dropbox - TrainingExample.xlsx

You just enter when each person did each course and have a threshold value of how many days until they need to retake the course.

The pivot table then summarises the data and show the minimum difference in days between the the course date and today then highlights in red people who need to retake the test

You're still better to do it in a Database application but this is probably the best approach to stick with Excel. It means you don't have to create new columns (fields) when you add people or courses and the pivot table will automatically include new people and courses.
 
Last edited:
Yes, access would be the best program to use in this case, if you have it power view could be used to model and slice the data for presentation (it can handle relationship data) and present this for you.

Or be truthful, do you really need the old data if it's been replaced by a recent training course? Simplify, simplify, simplify, is my mantra.
 
Yes it would be a lot simpler if it was just the 'current state' rather than being both a current state and historic record.
 
Yes it would be a lot simpler if it was just the 'current state' rather than being both a current state and historic record.


That's why you separate out the raw data and the specific report to find when users must retake their tests.

Record the data in the simplest way and ignore presentation. Then use another tool such as a pivot table or powerpivot to report on this data and tabulate it into a report.
 
Yes indeed - store the data as simply/non-duplicated as possible then stick whatever 'view' on top to see it how you want.
 
Thanks for all the suggestions, it looks a lot more trickier than i first expected. I will watch a few videos on pivot tables and access to get a better idea of how they work. If i went down the excel route it sounds like i need a data pool sheet as such and another sheet to display the info to the end user. My colleague might be overwhelmed with the process (they aren't great on computers). Either way, i have a better understanding of what i need to read up on.

Thanks again for all the suggestions, its much appreciated!

Thanks
 
With the pivot table solution they will just be adding rows to that table and then when you hit 'refresh' on the table, it will be updated with the latest info. You will need to be careful with how you set up the link so that new rows are included in the pivot table - i.e. an expanding range or range covering the whole columns.

You can put the pivot table on the same sheet or another one - or probably even a different workbook, depending on the use cases.
 

The latest video from AVForums

Is 4K Blu-ray Worth It?
Subscribe to our YouTube channel
Back
Top Bottom