Excel help required

MarcLister · 1 Apr 2011 at 21:42

I need to develop an Excel spreadsheet for some data handling duties I'll be taking on in a school from September onwards.

We need to be able to input levels of achievements in different subject by our students into SIMS. To make my job of doing this easier I want to develop a spreadsheet that will allow teachers and teaching assistants enter the correct data in a way that suits me.

We have a table of levels, descriptors and additional descriptors for the subjects we teach. Different levels in different subjects have different numbers of descriptors (within a level) and additional descriptors (from the level above the one the student is currently achieving).

I want to make the spreadsheet offer the correct number of descriptors and additional descriptors for each subject, and units within those subjects that have them by way of drop-down lists.

So, for example, in English the level P5 has 5 descriptors in Unit 1, 4 in Unit 2 and 2 in Unit 3. Should a member of staff select P5 for the level of English achieved in Unit 1 then the next column in the spreadsheet (descriptors) should have a drop-down list that offers the following numbers to be selected: 0, 1, 2, 3, 4 and 5. Similarly if P5 is selected for Unit 2 in English then the numbers available in the the descriptors column adjacent would be: 0, 1, 2, 3 and 4.

I have entered the table of data into Excel on its own worksheet and I've named the range, "Levels_and_Descriptors". A copy of the table is shown below. What I would appreciate is some help to develop a formula to allow this to happen. In terms of pseudo code in the descriptors column I'm thinking of:

Code:

1. Have a look at cell C4 and see what level has been selected.
2. Look for that level in range, "Levels_and_Descriptors". Then for English Unit 1 see how many descriptors there are.
3. Return those numbers to the drop-down list with 0 at the beginning of the list.

I've had a quick look for a formula that I think I might need. VLOOKUP isn't advanced enough for what I need so having found this link I think I will have to get my head around the MATCH and OFFSET functions.

What I can't work out right now is once I use MATCH and OFFSET to tell me how many descriptors can be use is how to persuade Excel to populate a drop-down list in the Descriptors column for English Unit 1 with the numbers of descriptors allowed for that level and unit.

Any ideas how I can link a drop-down list to a table and get Excel to populate the drop-down list with the right number of descriptors according to the level selected in the adjacent column?

As promised, here is the table to try to demonstrate what I mean by levels and descriptors/additional descriptors. English, Maths, Science and RE will be the more complicated subjects to sort this out for as they have multiple units and therefore more chance of varying numbers of descriptors/additional descriptors. The other subjects, being single unit subjects, should be a bit easier.

Now, quite literally, as I wrote the paragraph above I had a brainwave! Perhaps I should split the table below into a table for each subject? So that I can refer to a table dealing with a single subject and not with a massive table like I have now?

If you've made it this far, thanks and apologies for the humongous post!

suarve · 1 Apr 2011 at 22:57

I did something like this many moons ago at uni 0- except it was for a football league - used a lot of vb in the background

I'll have a look on an old backup drive as it may be helpful to you

MarcLister · 1 Apr 2011 at 23:14

Hi suarve. That'd be fantastic. How similar was your spreadsheet compared to what I need? Was it as complicated or less so?

And do you think my splitting the table up into a table for each subject would make things easier for me?

Morba · 2 Apr 2011 at 01:15

A nice vb front to it all would be beneficial. If i find some time in work next week, and suarve hasn't found his, I'll see what I can knock up for you.

One thing to remember, its great having the data put in in a format that you prefer, you just have to remember that it has to be easy as possible for the person entering the data!

MarcLister · 2 Apr 2011 at 09:59

Morba said:
A nice vb front to it all would be beneficial. If i find some time in work next week, and suarve hasn't found his, I'll see what I can knock up for you.

That would be very kind of you.

I don't have much experience with VB so could you explain how a VB front would be beneficial? And to whom? Me or the staff entering data? Or both?! :eek:

Morba said:
One thing to remember, its great having the data put in in a format that you prefer, you just have to remember that it has to be easy as possible for the person entering the data!

Of course. I need the data in a format I can deal with but to make this spreadsheet usable I need to make it as easy for the persons entering the data.

Tomsk · 2 Apr 2011 at 17:40

You can use "=INDIRECT(ref)" as the source for the drop down list.

MarcLister · 2 Apr 2011 at 18:04

Cheers Tomsk. Will have a play with that.

I've also been thinking about how I could use the final spreadsheet to create copies of itself but for each copy to have the name of a student as its filename using some kind of VB/macro attached to a button and getting the names of students from a list on a sheet.

So say there's the following students in a list:

Code:

Marc Lister
Louise Redknapp
Rosie Jones

then Excel would make copies of itself and would name the three spreadsheets:

Code:

MarcLister.xls
LouiseRedknapp.xls
RosieJones.xls

Could this be done by the spreadsheet I'd be using for the data entry or would I need another spreadsheet to make copies of a target spreadsheet for this? I'm thinking that if I can find a way to do this then it could save someone, probably me, loads of time each September when we get our new intake of students.

I wouldn't want to have to copy X spreadsheets and rename each one individually when I could give Excel a list of names and tell it to make copies of a spreadsheet for me naming each one after a student in the list. :cool:

Souleh · 4 Apr 2011 at 10:38

Yes you can make excel write any amount of files with any amount of validation in them!

I have been doing a lot of VBA scripting to make reports from SAP outputs (Think hundreds of thousands of rows accross up to hundreds of colums)

Ill have a deeper look at what you need when I get a bit more spare time but I can happily help you develop something useful to save you some time with any luck!

In not sure how friendly you are with VBA in excel but if youre going to be doing a lot of spreadsheets then it may be worth learning as it will save you hours if not days of reporting time - Typical reports here would take a full day to complete wheras a script with the correct validation can do it in under 4 minutes with outputs to .txt, .doc and .xls

MarcLister · 4 Apr 2011 at 20:39

Souleh said:
Yes you can make excel write any amount of files with any amount of validation in them!

I have been doing a lot of VBA scripting to make reports from SAP outputs (Think hundreds of thousands of rows accross up to hundreds of colums)

Ill have a deeper look at what you need when I get a bit more spare time but I can happily help you develop something useful to save you some time with any luck!

In not sure how friendly you are with VBA in excel but if youre going to be doing a lot of spreadsheets then it may be worth learning as it will save you hours if not days of reporting time - Typical reports here would take a full day to complete wheras a script with the correct validation can do it in under 4 minutes with outputs to .txt, .doc and .xls

Hi Souleh. Cheers for the reply.

My VBA knowledge is quite basic.

It is one thing I should try and improve on, perhaps I need to find some books on VBA and have a play around.

For the replicating of the spreadsheets we're talking no more than 25 a year. It might even be worth me investigating the possibilities of putting together a batch script to get the names of new students from a text file and making a copy of the target spreadsheet for me and naming each one after a student in the list.

I spoke to a colleague today and found that no matter what level a student achieves nor how many descriptors they have they can only have 10 additional desciptors. This little discovery means that I only need one column of numbers to be filled in with certain numbers according to the level selected immediately before.

I will have another play with the INDIRECT formula and see if I can get it working.

MarcLister · 4 Apr 2011 at 21:31

I've just had a quick look at http://www.contextures.com/xlDataVal02.html and from what I understand of that I need to have the number range actually written out. For example if someone chooses level P4 for Maths 2 I need to have 0, 1, 2, 3, 4 and 5 written out to make the dependent list work?

I can't work out how to tell Excel to return each individual integer from 0 inclusive all the way up to the limit for that level and unit into a dropdown list.

Working this out would be a tidier way than the 0, 1, 2, 3, 4 and 5 written out method.

MarcLister · 4 Apr 2011 at 22:36

Using VLOOKUP I can get Excel to tell me for each unit/level how many descriptors there are. I might use this in another column adjacent to the descriptors column so I can tell the user the maximum number of descriptors they have to choose from considering the level they've just chosen for that particular unit.

http://www.mrexcel.com/archive/General/12479.html - I've found this and it looks a bit like it might help me. It is VBA though! How would I feed something like that into a dropdown list and use the VLOOKUP formula I've just written as the top number in the integer array?

I have uploaded the spreadsheet if anyone would be so kind as to have a look at it and try and get the dropdown lists to have the right number of integers from 0 or 1 to whatever the maximum number of descriptors is listed for that level and unit. I've removed the names of my colleagues for privacy reasons but the dropdown list for that does work.

Before I forget I should tell you that the spreadsheet is in Excel 1997-2003 format (.xls). We don't have Office 2007/10 so we need to keep the file format .xls.

Tomsk · 4 Apr 2011 at 22:44

The formulas:

The result:

B1 varies the length of the drop down list.
C1 calculates the range for the drop down list.

HTH

MarcLister · 4 Apr 2011 at 23:03

Nice one Tomsk.

So if I use the VLOOKUP function to tell me how many descriptors there are I can use that value to act as the B1 value you've got?

24 seems to be the highest number of descriptors available so I could just do what you've done in column A and have 1 to 24 listed as a kind of sample range and use B1 to indicate how many integers in that range to use then use C1 to take those integers required and put them into an INDIRECT function in a validation list box?

Using the maximum descriptors in a VLOOKUP function might be a bit of a cheat but I can present that number to the user as a guide to the maximum number of descriptors available to them.

EDIT: I think it FLIPPING works!!

Thanks so much guys.

I've got it to work for Unit 1 English. I'll Dropbox the file to myself and add the above from Tomsk in for the other subjects.

Tomsk · 4 Apr 2011 at 23:17

Tomsk · 4 Apr 2011 at 23:30

Or if your lists are not consecutive numbers, you can use B1 as an index.

MarcLister · 4 Apr 2011 at 23:42

Ah. So instead of having a list of 1 to 24, I could just have a list of each unique number in the data table? So 2, 3, 4, 7, 12, 16, 24 etc? And index them?

It works as it is now with consecutive numbers so I'll carry on this way until the spreadsheet is finished, then I might try this way and see if its more efficient and worth the effort.

Thanks again Tomsk.