INDEX

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Link Round Up

Time for a link dump from my browser – clearing out tabs.

2010-07-31 Strobist Shoot Toorbul 162Education

How to Create a Portfolio with Evernote (Education Series) – Evernote is an awesome app – We use it all the time on various android and windows platforms.

Social Marketing and Web Development

Building trust - The Connection Constant

Want to build a online store? Choosing a payment gateway

Facebook – how to convert your personal account into a Facebook Page - for all those people who created a “person” for their business page.

More how to convert Facebook profiles into business pages including tricks and traps

Drupal

Configuring and adding images to Drupal 7 content with Wysiwyg, IMCE and Lightbox2 – very good tutorial

Excel

Probably the best explanation of using INDEX function for non volatile dynamic range creation in Excel

Windows 8

There will be lots coming on Windows 8 on this blog later – for now heres a very good read about how Windows 8 can pool data storage

Business

Realities of Business is a new website by a great friend to promote his new book. Worth a read! Buy it now!

Photography

Two local photographers worth checking out.

Helen Graham

Dale Travers

jethro's picture

Round up of Excel Tips and Hints

As Office 2010 becomes more prevalent in the workplace (most of our clients either have or will be soon updating to it) so the use of the ribbon has become more natural and intuitive for people. It is certainly much more intuitive than the old menu driven interface.

rh2-150x150Play a fun game called Ribbon Hero 2 that allows you to learn the fastest and best ways to do various common tasks in the various Office Apps. My kids are using this to learn the interface and some of these common tasks. I have even learnt some things in the 5 minutes I have spent playing it so far! Download Ribbon Hero 2.

Take the first step in growing your Excel Skills. Microsoft have created a series of learning videos that assist new users to Excel to grow their skills

Learn how to create your own Excel Macros with a free training course. Ideal for the entry level person who wants to explore macro writing for the first time. Note this doesn't create efficient code, but its a start to understanding how the VBA code interacts with Excel.

PowerPivot for Excel - if you have looked or used PowerPivot (See my review of Power Pivot) than this page has some good links for additional resources.

jethro's picture

Excel Tools and News from the web

I have had a bunch of pretty cool Excel things to post up – and finally got around to clearing my flagged items and browser windows.

Conditional Formatting

excel 2007I have written a couple of articles on Conditional formatting in Excel 2007 with lots of readers comments and requests for help. They are the two most read articles on this site.

I was very interested then to come across this article on Joseph’s site by Amit Velingkar where he shows you how to change the automatic colour ranges that are used in Excel 2007 for conditional formatting. He even includes some VBA code for this.

jethro's picture

Developing in Excel 2007

I much prefer working in Excel 2007 to Excel 2003. Despite the issues with backward compatibility, there are a lot of advantages and benefits to using the new version.

Some little things that have been changed are

The previous limit on nested brackets in formulas from 7 has been increased to 64. I used this today

The number of columns and rows has increased. I used this today.

I had to develop a file for a client that involved a complex work roster arrangement to calculate days off in repeating 2, 3 or 4 week cycles for the next 20 years.

Here is the nested formula that got me the logic for a roster.

=IF($X5>=AJ$4,$X$4,IF($Y5>=AJ$4,$Y$4,IF($Z5>=AJ$4,$Z$4,IF($AA5>=AJ$4,$AA$4,IF($AB5>=AJ$4,$AB$4, IF($AC5>=AJ$4,$AC$4,IF($AD5>=AJ$4,$AD$4,IF($AE5>=AJ$4,$AE$4,IF($AF5>=AJ$4,$AF$4,$AG$4)))))))))

I then used one formula to generate over 600,000 cells and create a map that looks like this.