Spy Journal 3.0 Filtering the web for you

Sometimes i need to just dump stuff out of my head. Sometimes my kids just say the funniest random things. Judith went out to my daughter at the clothes line couple of days ago while she was hanging out clothes to get a peg. Jadeen just randomly said “I am a stegosaurus”. That phrase has been bouncing around this house since then amidst lots of laughter.

Our brains are amazingly complex things. Sometimes we just do no appreciate the incredible processing power of simple things like coordinating even our vocal cords, diaphragm, lungs and mouth and tongue just to say a simple word. I was listening to Doctor Karl on Triple J explain how Turrets Syndrome caused people to swear occasionally (its not the most common symptom of Turrets and most people with it don’t do this) and he just reminded me to think about how our brains work.

Last night I dream about alien people and apparently asked out loud for a sword (according to Jude).

Yesterday I was able to run 3 kms with no knee issues – I wish I was back to the running ability I had back in the year 2000. However I’m coming to terms with being 12 years older then I was then and my running is fine as long as I don’t try and run like a 32 year old any more. I did a 5km race a couple of weeks ago and managed a personal best in the last 5 years or so so I am slowly getting it back.

Do you run? if yes where and when, if no – why not? Get into the comments or hit us up on Facebook.

My cycling has improved a lot since I got the new road bike and have been focusing on my hill climbing. My Mountain biking climbing endurance has improved as a result. Unfortunately my Gary Fisher Fat Possum broke in half on my last ride. I am waiting on Trek to honour the lifetime warranty and replace her.

Work has been interesting of late. Since I am no longer employed by IT Integrity and have gone back to being my own boss the pressure on my time has reduced. I am doing far more photography, as that is part of our business and we have had a lot more work in that area. I am doing more of the things I love in IT, web design and Excel work. I am enjoying learning new photography and Photoshop processing techniques. I am really enjoying the sports shooting we do as well.

I bought a new phone. After a disastrous attempt to get an iPhone to work without using iTunes I bought a new Samsung Galaxy IIS and am loving its ease of use, its synchronisation with my Google account and my other android devices, the HTC Desire S and the Motorola Xoom 2 tablet. Its great having all my photos from each device synch seamlessly together with Dropbox, my notes in Evernote all synch across all my devices and it works as a phone out of the box without having to connect it to a computer.

I’ll leave you with a selection of my favourite photos taken recently.

A while ago a reader named Jeremy sent me a request. Sorry I took so long to respond Jeremy – but I’ve finally done this and here are the results.

How can I delete an entire row based on the font color in column A?

I want do delete every row between A2 and A1000 where the font color is black or automatic. I have tried various alterations of the codes below.

Jeremy supplied several procedures, none of which worked correctly.

I have built a very simple version that does what is needed. I know that this is not good code, but it provides a good starting point. And it works. Anybody who needs to use the guts of it (the selection and deletion based on the font colour) can take that and apply it to whatever ranges they need to.

Here is the scenario.

1. You have a range of cells that have been coloured with the automatic font colour. (this could be any colour – you would just need to change the colour index in the code.)
2. You have some items that have been coloured with a different colour.
3. You need to delete the entire row of any cell that has not been coloured specially, ie all the automatic colour cells.

While looking at previous posts to choose a different function for this weeks post I noticed that I have never written a post specifically about the most commonly used function in Excel, the SUM function.

So today’s post remedies that. Note this post is based on Excel 2010. Most of this is still relevant in Excel 2003 and Excel 2007 is largely the same as 2010. The main difference is the ribbon references in Excel 2007/2010 vs the toolbar in 2003.

First the Official Excel Help information.

Description

The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

SUM(number1,[number2],...])
The SUM function syntax has the following arguments:
number1    Required. The first number argument that you want to add.
number2,,...    Optional. Number arguments 2 to 255 that you want to add.

Remarks

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.

As the title says this just an introduction. Its aimed at non power users who have recently moved to Office 2010 or 2007 from Excel 2003.

The main reason for this article is to demonstrate the basic use of the new table features and the ribbon features available to users.

I am going to start with showing you how to make a table from existing data and demonstrate a few of the ribbon features. I wont be going into anything too complicated in this post. Depending on feedback I might write some more specific detailed feature articles on table components and using tables later including converting tables back to ranges..

So how do we make a table – its actually very simple. In the following screenshots you will see a fairly typical spreadsheet with some information in it. I will show you how to turn that into a table.

Select any cell in the table.

From the Home Tab on the Ribbon click on the Format as Table Button.