Barrie has attended:
Office 365 End User course
VLOOKUP
Hi - I have an Excel document with 2 worksheets. One workshop contains a list of all courses we run in our learning and development department and next to each course, who the target audience is. So next to one course there may be several job roles that it is targeted at, for example. If I had a course of 'Telephony Training' in cell A1 then in cell A2 it may be listed as Team Leader, Trainer, HR as 3 job roles that will need to complete that training. This will be the same for every course moving down the worksheet. On a separate worksheet I will simply have a drop down option and listed will be all the job functions in the business, by selecting just one job role from this list I want it to then display all courses relevant to that role as defined on the other worksheet.
RE: VLOOKUP
Hi Barrie, thanks for your interesting post. Having more than one job description in a cell poses some difficulties in finding all the courses relevant to just one of those jobs. But I have a few suggestions.
Firstly, although it might be time and effort consuming, it would be good if you could have two itemised columns where each cell in column 1 contains a course and the cell next to it column 2 contains a single job description benefitting from that course. It will result in a long list but it makes it easier to work with.
Secondly, I would use the following Excel features to make this work:
1. Data Validation (drop-down list)
2. Advanced filtering (instead of Vlookup)
3. A simple macro assigned to an action button which applies the filter, then copies and pastes the results into the other sheet.
Which of the above three features are you not familiar with? I'm just asking so that I know where to go into more detail with my solution.
I hope to hear from you soon.
Kind regards
Marius Barnard
Excel Trainer
