In this blog post I am going to demonstrate a simple workbook where you can create or delete projects and add "next" actions to each project. You can also mark actions completed.

"The Getting Things Done method rests on the idea that a person needs to move tasks out of the mind by recording them externally, so the mind is free from the job of remembering the tasks that need to be completed. One can concentrate on performing the tasks, instead of remembering." Source: Wikipedia

Add a project

Delete a project

Select a project

Add actions to a project

Mark an action completed

Download excel *.xlsm file

Getting things done - template.xlsm

I read this post Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel automatically making column widths too wide when using urls in pivot tables. Stacey Armstrong demonstrates how to disable this setting.

Lindsay Hughes commented:

I can´t make it the default setting but the code below automatically disables this setting for all pivot tables in an activated sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        pt.HasAutoFormat = False
    Next pt
End Sub

Where to copy code?

1. Open VB Editor (Press Alt+F11)
2. Double click on Thisworkbook in the project explorer window
3. Paste code to module
4. Return to excel

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes data for excel pivot tables.

VBA macro

1. Press Alt+ F11
2. Right click your workbook in project explorer
3. Click Insert
4. Click Module
5. Paste macro code below to module
6. Exit VB Editor

Sub NormalizeData()
Dim Rng As Range
Dim WS As Worksheet
On Error Resume Next
Set Rng = Application.InputBox(Prompt:="Select a range to normalize data" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
If Rng Is Nothing Then
Else
    Application.ScreenUpdating = False
    Set WS = Sheets.Add
    i = 0
    For r = 1 To Rng.Rows.Count - 1
        For c = 1 To Rng.Columns.Count - 1
            WS.Range("A1").Offset(i, 0) = Rng.Offset(0, c).Value
            WS.Range("A1").Offset(i, 1) = Rng.Offset(r, 0).Value
            WS.Range("A1").Offset(i, 2) = Rng.Offset(r, c).Value
            i = i + 1
        Next c
    Next r
    WS.Range("A:C").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End If
End Sub

Download excel *.xlsm file

Normalize data.xlsm

Anees asks:

Answer:

The array formula becomes quickly complicated if I try to concatenate the start and end date in one cell. I am going to use two cells.

Date in cell A1: 

2012-01-01

Array Formula in cell B1:

How to create an array formula

1. Select cell B1
2. Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula in cell B1

1. Select cell B1
2. Copy (Ctrl +c)
3. Select cell range B2:B11
4. Paste (Ctrl + v)

Formula in cell A2:

Copy cell A2 and paste down as far as necessary.

Explaining array formula in cell B1

Step 1 - Compare dates and check if months are not equal

=IF(MONTH(A1)<>MONTH(A1+6), formula_TRUE, formula_FALSE)

becomes

=IF(MONTH(40909)<>MONTH(40909+6), formula_TRUE, formula_FALSE)

becomes

=IF(MONTH(40909)<>MONTH(40915), formula_TRUE, formula_FALSE)

becomes

=IF(1<>1, formula_TRUE, formula_FALSE)

Step 2 - Calculate days to last day in week

A1+MAX(IF(WEEKDAY(A1+{0, 1, 2, 3, 4, 5, 6})=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX(IF(WEEKDAY(40909+{0, 1, 2, 3, 4, 5, 6})=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX(IF(WEEKDAY({40909, 40910, 40911, 40912, 40913, 40914, 40915})=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX(IF({1, 2, 3, 4, 5, 6, 7}=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX({"", "", "", "", "", "", 6})

becomes

40909+6

and returns 40915 (2012-01-07) in cell A2.

Step 3 - Calculate last day in month

Example in cell B5

=IF(MONTH(A5)<>MONTH(A5+6), formula_TRUE, formula_FALSE)

becomes

=IF(MONTH(40937)<>MONTH(40943), formula_TRUE, formula_FALSE)

becomes

=IF(1<>2, formula_TRUE, formula_FALSE)

DATE(YEAR(A5), MONTH(A5)+1, 1)-1

becomes

DATE(YEAR(40937), MONTH(40937)+1, 1)-1

becomes

DATE(2012, 2, 1)-1

becomes

40940-1

and returns 40939 in cell B5.

Download excel *.xlsx file

Anees.xlsx

Phil asks:

Answer:

Create drop down list

1. Go to tab "Data"
2. Click "Data Validation" button
3. Go to "Settings" tab
4. Select List
5. Type 2011, 2012 (sheet names) in Source:
6. Click OK

Macro code

1. Press Alt+ F11
2. Right click your workbook in project explorer
3. Click Insert
4. Click Module
5. Paste macro code below to module
6. Exit VB Editor

Sub AddValues()
Dim i As Single
i = Worksheets("" & Range("D2")).Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("" & Range("D2")).Range("A" & i & ":C" & i) = _
Worksheets("Enter Data").Range("A2:C2").Value
Worksheets("Enter Data").Range("A2:C2") = ""
End Sub

Create button

1. Go to "Developer" tab
2. Click "Insert" button
3. Click "Button" (form control)
4. Assign macro AddValues()
5. Click OK

Download excel file *.xlsm

Add values to different sheets.xlsm