Wednesday, June 21, 2006

Using Outlook and your intranet to collaborate on Excel files

A lot of people attach files that they are working on to an email and send them to their colleagues for review and modification. There are numerous reasons why this is not a good practice including:
- clogging up the email system (each recipient receives the full file and if they return it the full file is sent back as well)
- this makes it hard to synchronise different changes from different people
- if people open files from their email and then modify them they are actually opening a file that Outlook has saved to a temp folder. Any changes are made to this file and can be lost easily.

There is a far better alternative. When you are working in an Excel file you can actually find the full intranet address in the web toolbar. To display this toolbar right click somewhere in the toolbar area and then select the web toolbar from the list. In this toolbar you will notice an address bar (similar to in your browser). Click in this address field to select the file's full path and name. Use Ctrl C to copy and then go your email. Do NOT paste into your email as this will probably not translate correctly. Instead choose Insert Hyperlink from the menu. Now paste the address into this dialog box and click OK.

This will paste a link to the file on your intranet. When you send this email the recipients can click that link and open the file directly on your intranet. Of course this is dependent on their intranet access and security levels. It will also not work if you send it to people outside your organisation. However it is a far better alternative in most cases to attaching the file and forwarding it. Your Exchange administrator will love it too!


Posted by: Jethro | 3:08 PM |

Tuesday, June 13, 2006

Su Doku puzzle

From Excel Blog


I read an article in the Denver Post this week regarding Sudoku Puzzles. I then completed several puzzles at websudoku.com.

You can create and solve sudoku puzzles using an MS Excel work book from Andy Pope: AJP Excel Information - Su Doku puzzle.

I also found a Sudoku puzzle solver at Office Online via Daily Dose of Excel.

More Sudoku linkes : http://www.sudokufun.com/ and http://sudoku.heebie.co.uk/

I have tried a few of these and they are great!


Posted by: Jethro | 9:57 AM |

Thursday, June 08, 2006

Google Spreadsheet

I don't think Microsoft has anything to be worried about by the new Google Spreadsheet. Google have created a free online spreadsheet system. It is in a limited trial and that is reserved on a first come first served basis. You will need a Google account (eg Gmail, Google Reader etc)

Limited reviews can be found on Dick Kusleika's site and John Walkenbach's site.

Update: Reality Me has some users who have commented on it also.


Posted by: Jethro | 3:49 PM |

Extended Conditional Formatting

Excel allows you to specify up to three conditions under Format | Conditional Formatting.

Bob Phillips of xlDynamic.com has written an AddIn that extends the number of conditions to 30!
Co-written by the late Frank Kabel, Bob has made this Extended Conditional Format AddIn available for free on his website.


Posted by: Jethro | 3:37 PM |

Tuesday, June 06, 2006

Formatting Cells Containing Formulas

Here is an excellent tip from ExcelTip.com

To format cells containing Formulas using the Conditional Formatting dialog box, add a VBA Function:

Step 1: Add the following VBA Function to a regular Module:

Function IsFormulaInCell (Cell) As Boolean
IsFormulaInCell= Cell.HasFormula
End Function

Step 2: Use the Custom Function to identify and format cells containing formulas:

1. Select the cells in the sheet by pressing Ctrl+A, in Excel 2003 press Ctrl+A+A from a cell inside a region.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula is from the dropdown list.
4. In the formula box, type =IsFormulaInCell(A1), and then click Format.
5. From the Font tab, select any desired color and click OK twice.


Posted by: Jethro | 6:52 PM |

Monday, June 05, 2006

Website Overhaul Plans

I am planning a major overhaul of this website in the near future.

There are a number of reasons for doing this, in no particular order:
Its time for a change - this website is almost 2 years old in its current format
I want to use Drupal
I want to give users better access to archives, search functionality and categorisation and indexing
I want a better image gallery option
I want interactive polls and forums included
I want to allow users the ability to contribute also

However I also want to make sure that I involve my readers - thousands of them - thanks for coming and reading.
To achieve this I want you to assist me with the makeover by having your say about what you like, dislike etc about the site.
I am posting this article on each part of the Spyjournal website.

So email me or comment below this article with some answers please
Do you read by RSS or on the web page? (RSS Readers - please take the time to either email me or comment)
Which browser do you use?
Do you come regularly (book marked) or did you come from a search engine?
Would you like to contribute to this site?
What do you like most?
What do you hate the most?
Anything else you can think of?

Thanks and I will keep you updated. When I have a beta version up I will invite you to review it.


Posted by: Jethro | 2:45 PM |

Saturday, June 03, 2006

Office 2007 Windows Vista and Windows Server Longhorn Beta release

Microsoft have released the Beta for Office 2007 (Office 12). Bill Gates addressed a conference where he announced the release of Office, Vista and Longhorn.

In his keynote address, Gates formally delivered the first beta 2 copies of Windows Vista, the 2007 Microsoft Office system and Windows Server “Longhorn” to a representative of Chevron Corp. Chevron is one of many customers conducting early deployment and testing on all three platforms to hundreds of desktops and servers.

Business customers worldwide will find tremendous value in deploying the products together. As the world of digital business information grows exponentially, businesses look to software advances not just to manage the influx of this information, but also to accelerate the effectiveness and impact of their people. Windows Vista, the 2007 Microsoft Office system and Windows Server “Longhorn” are designed to help businesses empower their people to manage waves of information and contribute to bottom-line success in four key areas:

• Simplifying how people work together. Wherever people work in an organization, these three products will make it easier for them to communicate and work together in teams and with their external customers and partners. The products include collaboration capabilities that enable workers to more easily share, access and synchronize information and documents across a wide range of devices.

• Enabling better content protection and management. Windows Vista, the 2007 Microsoft Office system and Windows Server “Longhorn” are designed to streamline everyday business processes and compliance procedures that slow people down. The products include capabilities that enable better protection and retention of user and system data, as well as improved application of compliance policies and business process integration with critical business systems.

• Finding information and improving business insight. Windows Vista, the 2007 Microsoft Office system and Windows Server “Longhorn” enable people across organizations to have faster, easier access to critical information and the tools that help them derive better insight from this information. The products include new tools for finding information across a range of applications, on desktop PCs and on mobile devices, while also extending business insight with new business intelligence tools.

• Reducing IT costs and improving security. Windows Vista, the 2007 Microsoft Office system and Windows Server “Longhorn” cut down deployment and management costs and complexity by reducing security risks and simplifying the deployment process. The products include capabilities that help IT professionals better manage operations, while also delivering enhanced protection from Web-based malicious software and fraudulent e-mail messages. In addition, the products include capabilities that enable companies to deploy a single operating system image, and streamline the deployment and customization of software updates.


Posted by: Jethro | 8:36 AM |

Thursday, June 01, 2006

Using the SUBTOTAL Function in Excel

The Subtotal function is commonly used when creating Subtotals on a table with the Data | Subtotals Menu command. However it can be just as easily used by writing the formula and has some powerful uses.

The syntax is SUBTOTAL(function_num,ref1,ref2,...)
The function numbers allow you to use any of the following functions in the subtotal:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

The most common is of course the SUM function.

When and why should you use SUBTOTAL and not just the SUM function on its own.


Posted by: Jethro | 8:58 AM |