This section focuses on the more advanced skills, formulas and functions you need to make an effective solution. The headings link to a page for each skill. It specifically looks at:
Drop down list validation: This limits the data people can have in a given cell, whilst letting them chose the allowed values from a drop down list
Named Ranges: This lets you refer to a group of cells by a single word rather than references. Useful when copying formulas.
Vlookup: Can be used to get the computer to automatically lookup and return (put in a cell) the right information. Works by looking at what has been entered in one cell. Say a1, then going and looking for that value in a pre-
If : Allow you to check if something is true (for example is cell over 500), then returns specific text or data if it is, and different text or data if it isn’t.Nested If : exactly the same as an if , except you have more than 1 check, for example (if cell is over 500, then say A, if not is it over 200 then say B, if it is not over 200 Say C)
If(iserror): Checks if the formula will return and error, and does something if it will. Used when you want to use the result of another formula in a calculation, and there is a chance it will be an error which would stop everything working. You can use this to put a 0 in the cell instead of the error code (makes more sense when you see it in use)
If (isna): Very similar to iserror, except it deals with cases when the result would be #NA
Set Print Area: A useful tool that lets you chose what will print. Can be used to allow notes on screen that won’t print out
Macros: These are simply a recording of a set of steps. They can be used to make something happen automatically
Buttons: Can be made and used to start Macros.
Goal Seek: If you know what number you want out of a formula, goal seek will help work out what initial numbers you should put in.