Data list set up

Microsoft Excel 2003 (full product)
February 16, 2010 at 00:39:57
Specs: Windows XP
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.

See More: Data list set up

Report •

February 16, 2010 at 07:09:41

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

	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
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


Report •

February 18, 2010 at 10:22:51
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.

Report •

Related Solutions

Ask Question