Applied ICT A Level Unit 10 - Advanced Spreadsheets
You will need to be able to produce a reusable spreadsheet system for a client of your choice. Your system will need to include at least five advanced spreadsheet features to help meet the specific needs of the user. You need to be able to do this, and document it, in a 20 hour exam period.
Checkout the list of advanced spreadsheet skills. Using five of these is pretty easy actually
Sections A to F are completed before the 20 hour exam period. These make up 32 of the 70 marks available for the unit. Each section has it's own page to guide you through the things you need to do to hit the marks - the nav bar will take you there.
This page has some testbeds and trial solutions to work on specific spreadsheet issues. Some of them may be helpful.
The Exam Marksheet Examples:
A simple exam grade boundaries spreadsheet (updated: now it copes with A* grades properly!)
This uses data transferred from another sheet, lookups, validation, customised error messages, error trapping, nested if statements, the use of named ranges in formulae, protection and hiding. In other words, at least 7 advanced skills from the exam board list as well as a user interface which meets the needs of the users.
And a slightly tweaked version to show a different way of doing it (also now updated to get A*'s right)
This is based on a version developed by someone on the Staffordshire Learning Net Geography forum
. I've adapted it to use named areas and to calculate the difference in grades. It uses lookups, error trapping and named areas, but could easily be developed to include protection and so on.
The Controlled Assessment Timekeeper:
A way of adding up the time students spend on controlled assessment tasks
This is simple enough. Just enter the names down the left and then put the minutes they spend on the task each session in the boxes (you could add a row to put the date in easily enough). The total time gets sorted by a use of =SUM(C3:V3)/60/24 and then formatting in [h]:mm format (use the Custom format option). This can be improved - I'll be seeking feedback on what people want it to do, so by all means let me know!
And a version which allows the amount of time spent at High and Low levels of control to be recorded
This is protected to remove the risk of accidental deletion of stuff, but there's no passwords involved so the protection can be removed if necessary. There are 20 sessions available. More columns could be added, but the High and Low sheets would also need more columns adding - I can do this for anyone who specifically needs it is they let me know!
Please provide feedback if there are problems with the sheet or if there's anything specific you want it to do - if you need me to explain how it works I'm happy to do that as well, although I can't promise that it'll be clear and coherent or anything...
The Animal Feeds Example
A problem came up. Someone wanted to make the contents of one drop down dependent upon the value selected in the previous drop down. Hmm - tricky.
But, actually, not that tricky - it simply requires a bit of Lookup madness!
A sheet to demonstrate how to make one drop down dependent on another
This is a bit complex. Feel free to ask me to go over it in more detail!
The Order Number Example:
A sheet to demonstrate how to produce an updated order number
This showcases a few basic (but effective) moves - how to get the current date in the sheet, how to calculate VAT using a named cell, how to (in theory) produce a macro to copy order details into a filing sheet and how to produce an order number that's unique to the order being dealt with (basically using a primary key as you would do in a database).
The order number was the main reason for producing it, to demonstrate one way of doing that (which is harder than it looks and involves a touch of concatenation as well!). There may well be other, funkier ways of doing this (including using VBA I know), but I wanted a nice simple method for this one. If you know a cooler way of doing it, please let me know about it!
Check it out - the sheet doesn't have any macros in it but is fully annotated.
The macro code for the copy, pasting and deleting macro would probably look something like this:
It's actually quite important to start recording it from the Order Details sheet so that you get the first line occurin' - otherwise when you run it the macro will insert the cells in the sheet your currently in (almost certainly the Order Details sheet) which would be bad.