Microsoft Excel 2003 (full product)

Hi

I have a large workbook which contains several worksheets - one for each year. I have realised that the way I have set it up is wrong. I have columns across the top which are headings such as Last name, First name, Date of Birth etc plus a column for various courses. This sorts ok but I somehow have to show whether each course is annual, bi-annual, tri-annual or one-off and also whether it is statutory, competency etc. I don't know how to set this up so that I can sort on any of these things - I can't see how to link annual, bi-annual etc to one particular course. It also has to be set up so that I can try and create a pivot table from it (that will be fun!). All help gratefully received.

Birdbrain

Hi, If you have a column with course names, then the next column could use a VLOOKUP() to lookup the course name in a table consisting of course names and course type

e.g.X Y 2 Math Annual 3 Advanced Math Bi-annual 4 Statistics Tri-annual 5 Banking meltdown One-off

If cell D2 contains a course name put this in E2

=VLOOKUP(D2,$X$2:$Y$5,2,FALSE)

Note the $ signs for the lookup table address

The formula will return the course type

2, in the formula means that the value is returned from the second column in the table. False ensures an exact match.

If a course name is not present you will get the #NA error.This formula can be copied to all rows in that column to return the course type.

The column can be used as the index column for a Sort

Regards

Thank you once again for your help. I am going to have a go at that and I'll let you know how I get on.

Birdbrain

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History