Hannah says, how do I concatenate a date into a sentence?

Ah, glad to hear from you Hannah. And, as usual, an excellent question.
Here's the problem - if you want to produce a concatenated sentence (that's one where words or cell contents are merged together into one cell) then if you try and include a date it won't copy the date in properly.

So, for example, if I wanted to say "This quote is valid for 7 days from 12 October 2010" then I would probably try and go:
="This quote is valid for 7 days from "&L47

But it won't work - it will give a five figure serial code for the date instead of the actual date.

So, this is what you do instead:

="This quote is valid for 7 days from "&TEXT(L47,'dd mmmm yyyy')

And it'll work.

A note

• if you want the date format 12-10-10 then go 'dd-mm-yy'
• if you want the date format 12 Oct 10 then go 'dd mmm yy'

and so on.

Hannah says you don't get nothing for a pair, not in this game.

Pasting date values

OK - so you know how to copy stuff from one sheet to another and probably know how to do the Paste Special > Values thing to get the actual cell values rather than the cell reference.

But, when you do that the formatting can go dodgy, dates in particular.

So, rather than pasting the values go Paste Special > Values and number formats instead. And it works.

Rock and roll.

Hannah decides to do some concatenation

Concatenation is when you link stuff together in a cell.

For example, you might link cells A2 and B2 together using

=A2 &" " &B2

The ampersands (the & thingie) do the linking. The " " just adds a space between the two cell contents. If A2 and B2 had forename and surname in then that might produce a more useful cell to use.

There's an example of how to use concatenation to produce a cool and funky order number at That Blue Square Thing. Check it out, it's cool and funky and there are no chickens to be seen.

Hannah says concatenation is cool.

e2a: I've not thrown the macro code in as a screenshot as well. Just because I'm feeling kind.

Testing and Test Plans and Tests and other duck stuff

Ooh, I love a test. Especially in the Unit 10 spreadsheet exam

There are 12 marks (out of 70...) for testing and test plans - so they're important! It's rows f and g of the markscheme you need to consider: row f is in the prep work; row g is in the exam.

1. Plan first:

a) You need (second row of f):

• a plan which shows how you will test each individual element of the spreadsheet. This means each time data is entered, a button pressed, an option chosen or a spinner or tick box or something clicked.
• this plan needs to be in a sensible order
• it needs to say what you're going to do, what data (precisely) you're going to test the spreadsheet with, and what exactly the expected output is (bear in mind, doing something on one worksheet might have an impact on several worksheets)

b) Then you need (first row of f):

• a plan for the whole system - i.e. taking a series of clients through the spreadsheet from start to finish as if they were doing it for real
• this needs to include the expected output as well
• there must be sets of test data (second mark requires this)

2. Do the Tests

For row g all you do is actually follow the test plan through and provide evidence of doing so. It's important in this bit to show whether or not the actual outcome is what you expected it to be.

This means for both parts of the test plan - the individual testing elements and the whole system testing

Here's a hint - this will need a bunch of screenshots and might take a while. Numbering your tests will make this a load easier.

You'll also want to look at Testing, Testing 124 which I blogged last year (it's about databases but it'll kinda work) as well as Normal, Extreme and Erroneous which I'll blog in a minute.

Hannah says dates in Excel are easy

Hannah says, if you want to get an updating date in Excel then it's quite similar to how it works in Access!

All you need is =now() in a cell and it'll sort out the date for you. You can format it to change what's included (Grace says you can also use =today() - but not =date() because that works differently.

You can also, just like in Access, use =now()+7 for example.

Hannah says you get the best of both worlds. Grace, otoh, says sweet niblets.

Designing - detail, detail, detail...

The key test for designs (and this is true for exams and portfolios) is:

could I (a skilled user) implement your design exactly as you have presented it working only from your designs and without having to make decisions myself?

In other words: does it tell me everything I need to know?

Everything.

Size, font, location, colours, macros, formulae. The whole banana. In enough detail.

It's fine to use more than one sheet of paper for each page. You might want one for the GUI and one for formulas for a spreadsheet. You might need another form macros. For a newsletter you may need one for layout and one for style.

The key is: is there enough detail (through annotations) for me to be able to produce it.

Here's one that someone, let's call her "Grace", made earlier.

Yeah, click it: it gets bigger...

It's *just* for the GUI - the layout of the sheet. The detail's OK - but she now needs to go on and get the details of the size for the boxes (by right clicking on her sheet she's designed and getting properties and the size tab), the width of the lines, the colour of the lines and the colour (a bright yellow) of the button.

To define the colour it'd be best to use a quantitative method - like using the RGB. You should be able to find a way of doing this (ask if you can't). In this case you can go to the lines/colors tab and go to more colors and you should be able to get the RGB from there:

Hannah says how do I find the command button in Excel 2007

This is specially for Hannah who couldn't find the command button...

http://office.microsoft.com/en-us/excel/HP102366761033.aspx

Aye carumba!

Hannah's Guide to Excel Keyboard Shortcut Heaven

Hannah says...

• Showing Formulas: Ctrl+Star Wars (the star wars button being the odd one without a name underneath Escape in the top left of the keyboard). Think: control the force... :-)
• Showing Macro Code: Alt+F11. You might then need to navigate to the different sheets and modules in the left window pane.
• Spell Checking: F7

Hannah says Cowabunga