Thursday, March 30, 2006

Common errors in Excel

Dick from Daily dose of Excel has written a post about a friend of his who is putting together a utility that will identify and possibly resolve a number of common problems with Excel spreadsheets. He has asked for input.
Here are some of the comments including mine.


My comments


If you have any additional pet whinges then either add them in the comments here or head over to Dicks site to add them.


Posted by: Jethro | 11:28 AM |

Monday, March 27, 2006

Creating Sheet Tabs in Excel with the Number of the Week

From Unoffical Office Stuff


Excel no longer has a theoretical limit on the number of worksheets in a workbook. One common use of this ability is to add a worksheet for each week in the year.

Here's a macro that does the trick:

Sub YearWorkbook()
Dim iWeek As Integer
Dim sht As Variant
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=(52 - Worksheets.Count)
iWeek = 1
For Each sht In Worksheets
sht.Name = "Week " & Format(iWeek, "00")
iWeek = iWeek + 1
Next sht
Application.ScreenUpdating = True
End Sub


Posted by: Jethro | 2:01 PM |

Friday, March 24, 2006

Disabled Items in Excel - AKA crashed files

From Dicks Blog

It turns out that Excel maintains a list of Disabled Items that you can enable by going into Excel’s menu:
Help > About Excel > Disabled Items…
Then highlight the items and click Enable.
Useful if you need to re-enable or delete a crashed Excel file.


Posted by: Jethro | 2:00 PM |

Sunday, March 19, 2006

Dividing a First Left Heading Title

From Exceltip.com

To divide a title in a cell:

1. In cell A1, enter the words: Headings and Invoice Number.
2. Select cell A1, and in the Formula Bar, place the cursor after the word Headings.
3. Press Alt+Enter twice (once to wrap the text and once for an additional empty row space).
4. Press Ctrl+Enter to accept the changes without moving from cell.
5. Press Ctrl+1 to open the Format Cells dialog box.
6. Select the Border tab and select the left diagonal border.
7. Select the Alignment tab.
8. From both the Horizontal and Vertical dropdown lists, select Justify.
9. Click OK.
10. In the Formula Bar, place the cursor before the word Headings and press the Space Bar to add spaces and move the word to right.


Posted by: Jethro | 9:42 AM |

Saturday, March 11, 2006

Help Needed

I am in need of a short term Excel person to assist me with a project.

It will be for around 4 weeks and I am prepared to pay around $40 per hour.
I would prefer someone with some BA experience, but am happy with a graduate or a less experienced person who can take direction well.
The job will not be supervised, but will be managed by myself.
Location is negotiable, e.g. can work from home with some time required to be on site, both at the clients and at my office in Narangba.

Excel skills need to be intermediate / advanced to Guru and if you have VBA that is good but not essential. You will be expected to know and understand multi level formulas, array formulas and range name substitution in formulas. Skill with data validation, filters and subtotals is also needed.


Posted by: Jethro | 4:51 PM |

Monday, March 06, 2006

Constructing multi conditional array formulas in Excel

That heading might sound like this is way over your head, but I bet you've probably needed to write formulas like this before and never knew how to do it.

Let me illustrate with an example.
Today I needed to use an array formula to find the largest value in one column, after conditionally filtering by two other fields.

OK now in English!

Say you have a table of payroll data and you need to find who got paid the most from one particular state where the monthly salary paid was over a certain threshold.

Column A = State
Column B = Monthly Salary
Column C = Weekly Pay
Column D = Name

The formula I wrote was {=VLOOKUP(MAX((A3:A100="WA")*(B3:B100>2500)*C3:C100),C3:D100,2,FALSE)}
This formula is committed (and edited if necessary) with CTRL SHIFT ENTER and that is how you get the curly brackets.
This returned the name of the employee who was paid the most from WA state from the list of employees in that state who earned over $2500 per month.

However while this formula works fine you will generally find that with this sort of data the employee name is in the first column. Regardless, often the results you need are not able to be found using VLOOKUP.
Here is the way to find the same result where the columns are as follows:
Column A = Name
Column B = State
Column C = Monthly Salary
Column D = Weekly Pay

{=INDIRECT(ADDRESS(MATCH(MAX((B3:B100="WA")*(C3:C100>2500)*D3:D100),D3:D100,0)+2,1),TRUE)}
Once again the formula needs to be committed with a CTRL SHIFT ENTER.


Posted by: Jethro | 1:06 PM |