Answer:

Sheet2

Array formula in cell E2:

How to create an array formula

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

How to copy array formula

1. Select cell E2
2. Copy cell (Ctrl + c)
3. Select cell E3:E20
4. Paste (Ctrl + v)

Download excel *.xlsx file

SBabu.xlsx

Related posts:

1. Excel udf: Lookup and return multiple values concatenated into one cell
2. Vlookup of three columns to pull a single record
3. Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
4. Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
5. Lookup values in a range using two or more criteria and return multiple matches in excel

BasicUDF(range) removes blank cells.

The values are returned horizontally and #N/A are returned when there are no more values.

User defined function

Function BasicUDF(Rng As Range)
Dim Cell As Variant
'Create an array
Dim temp() As Variant
ReDim temp(0)
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last value in array
        temp(UBound(temp)) = Cell
        'Increase array size with 1
        ReDim Preserve temp(UBound(temp) + 1)
    End If
Next Cell
'Remove the last value in array
ReDim Preserve temp(UBound(temp) - 1)
'Return values
BasicUDF = temp
End Function

BasicUDF1(range) also removes blank cells.

Values are transposed vertically.

User defined function

Function BasicUDF1(Rng As Range)
Dim Cell As Variant
'Create an array
Dim temp() As Variant
ReDim temp(0)
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last value in array
        temp(UBound(temp)) = Cell
        'Increase array size with 1
        ReDim Preserve temp(UBound(temp) + 1)
    End If
Next Cell
'Remove the last value in array
ReDim Preserve temp(UBound(temp) - 1)
'Transpose temp array from horizontal to vertical and return values to sheet
BasicUDF1 = Application.Transpose(temp)
End Function

BasicUDF2(range) removes blank cells.

Values are transposed vertically, #N/A are now replaced with blank cells.

User defined function

Function BasicUDF2(Rng As Range)
Dim Cell As Variant
Dim row As Single
'Create an array
Dim temp() As Variant
ReDim temp(0)
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last value in array
        temp(UBound(temp)) = Cell
        'Increase array size with 1
        ReDim Preserve temp(UBound(temp) + 1)
    End If
Next Cell
'Add remaining blanks to array
For row = UBound(temp) To Application.Caller.Rows.Count
    temp(UBound(temp)) = ""
    ReDim Preserve temp(UBound(temp) + 1)
Next row
'Remove last blank
ReDim Preserve temp(UBound(temp) - 1)
'Transpose temp array from horizontal to vertical and return values to sheet
BasicUDF2 = Application.Transpose(temp)
End Function

BasicUDF3(range) removes blank cells.

Values are transposed to fill the entire selection, #N/A are replaced with blank cells.

User defined function

Function BasicUDF3(Rng As Range)
Dim Cell As Variant
Dim row, i As Single
'Create an array
Dim temp() As Variant
'Dim array with same size as selection
ReDim temp(Application.Caller.Columns.Count - 1, 0)
i = 0
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last value in array
        temp(i, UBound(temp, 2)) = Cell
        i = i + 1
        'Add a new row to the array
        If i = Application.Caller.Columns.Count Then
            i = 0
            ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
        End If
    End If
Next Cell
'Process remaining cells in selection
Do
    'Every remaining value is ""
    temp(i, UBound(temp, 2)) = ""
    'Current column number
    i = i + 1
    'Check if current column is equal to the number of columns in selection
    If i = Application.Caller.Columns.Count Then
        'Start over at column 1 (0)
        i = 0
        'Add a new row in array
        ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
    End If
Loop Until UBound(temp, 2) > Application.Caller.Rows.Count - 1
'Remove last row in array
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1)
'Return values
BasicUDF3 = Application.Transpose(temp)
End Function

Download excel *.xlsm file

udf basics.xlsm

Related posts:

1. Excel udf: Combine cell ranges into a single range while eliminating blanks
2. Excel udf: Filter unique distinct values (case sensitive)
3. Excel recursive udf: List files in a folder and subfolders
4. Extract unique distinct values from a filtered table (udf and array formula)
5. Excel udf: Reorganize data

See animated gif.

You can also select multiple cells in the table (Press and hold Ctrl when selecting cells).

Vba code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim c As Single
Dim str As String
'Iterate every cell in selection
For Each ACell In Target
    'Don´t stop if an error occurs
    On Error Resume Next
    'If selected cell is in a table and the table name is Table1, save TRUE in ActiveCellInTable (boolean)
    ActiveCellInTable = (ACell.ListObject.Name = "Table1")
    'Resume normal error handling (stop if an error occurs)
    On Error GoTo 0
    If ActiveCellInTable = True Then
        'Save cell reference (First column in table) if str is empty
        If str = "" Then str = "Table1[[#ALL]," & ACell.ListObject.Range.Cells(1, 1).Value & "]"
        'Calculate selected cell's column number in table
        c = ACell.Column - ACell.ListObject.Range.Cells(1, 1).Column + 1
        'Check if column number is above 1
        If c > 1 Then
            'Add cell reference
            str = str & "," & "Table1[[#ALL]," & ACell.ListObject.Range.Cells(1, c).Value & "]"
            'Change "Chart 1" data source
            ChartObjects("Chart 1").Chart.SetSourceData Source:=Range(str)
        End If
    End If
Next ACell
End Sub

1. Right click on sheet name: Sheet1
2. Left click on "View Code"
3. Copy/Paste vba code to module
4. Return to excel

Download excel *.xlsm file
Select a cell in a table to update a chart.xlsm

Related posts:

1. Excel vba: Filter a table using the selection change event
2. Change chart data range using a drop down list (vba)
3. Excel vba: Populate a combobox with values from a pivot table
4. How to create a dynamic pivot table and refresh automatically in excel
5. Change pivot table data source using a drop down list

"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

Related posts:

1. Advanced Gantt Chart Template
2. Win xp: Deleting a file or folder:Access denied error
3. Win xp: Free antivirus software
4. Excel vba: Select cell A1 on all sheets before you close a workbook
5. Monthly calendar template #2 in excel

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

Related posts:

1. Excel vba: Populate a combobox with values from a pivot table
2. Follow hyperlinks in a pivot table
3. Google spreadsheets: Pivot tables
4. Auto refresh a pivot table in excel
5. Auto populate a sheet

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

Related posts:

1. Excel vba: Save invoice data
2. Toggle a macro on/off using a button
3. Excel vba: Edit invoice data
4. Change pivot table data source using a drop down list
5. Categorize data entry values (vba)

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

Related posts:

1. Excel: Calculate last date of a specific month
2. Filter duplicates within same date, week or month in excel
3. Filter weeks from a date range
4. Highlight duplicates on same date, week or month using conditional formatting in excel
5. How to automatically calculate a specific day of a month

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

Related posts:

1. Add values to a table (vba)
2. Basic data entry (vba)
3. List all open workbooks and corresponding sheets (vba)
4. Quickly filter a column in an excel table
5. Toggle a macro on/off using a button

Sub EnterName()
Dim col, Lrow As Single
Dim tmp As String
col = Application.WorksheetFunction.Match(Range("C2"), Range("B4:H4"), 0) - 1
tmp = Range("B" & Rows.Count).Offset(0, col).Address
Lrow = Range(tmp).End(xlUp).Row
Range("B1").Offset(Lrow, col).Value = Range("E2").Value
Range("B1").Offset(Lrow, col + 1).Value = Range("F2").Value
Range("E2").Value = ""
Range("F2").Value = ""
End Sub

Answer:

Create drop down lists

1. Select cell C15
2. Go to tab "Data"
3. Click "Data validation" button
4. Select "List" in Allow: field
5. Select cell range B1:H1 in Source: field

Repeat above steps with cell C16 and cell range A2:A13
Insert macro

1. Press Alt+ F11
2. Right click on your workbook in project explorer
3. Click Insert
4. Click Module
5. Paste vba code to module window

Sub AddValue()
Dim column, row As String
Dim c, r As Single
With Worksheets("Sheet1")
If .Range("C15").Value = "" Or .Range("C16").Value = "" Then Exit Sub
column = .Range("C15").Value
row = .Range("C16").Value
c = Application.Match(column, .Range("B1:H1"), 0)
r = Application.Match(row, .Range("A2:A13"), 0)
.Range("A1").Offset(r, c).Value = .Range("C17").Value
End With
End Sub

Create button

1. Go to "Developer" tab
2. Click "Insert" button
3. Click "Button"
4. Create a button
5. Assign macro "AddValues"
6. Click OK
7. Change button text

Download excel *.xlsm file
Add values to a table.xlsm

Related posts:

1. Add values to a data validation list (vba)
2. Categorize data entry values (vba)
3. Apply data validation lists dynamically (vba)
4. Basic data entry (vba)
5. Dependent data validation lists in multiple rows

How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..

Answer:

Array formula in cell E3:

Formula in cell H3:

How to create an array formula

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

How to copy array formula

1.  Select cell E3
2. Copy cell (not formula)
3. Select cell range F3:G3
4. Paste
5. Select cell range E3:G3
6. Copy cells (not formulas)
7. Select cell range E4:G9
8. Paste

Explaining array formula in cell E3

See post: Create a unique distinct list and sort by occurrances from large to small

Download example *.xlsx file

Unique distinct records sorted by frequency.xlsx

Related posts:

1. Count unique distinct records in excel 2007
2. Filter unique distinct records with a condition in excel 2007
3. Excel 2007 pivot table: Count unique distinct records (rows)
4. Count unique distinct records with a date and column criteria in excel 2007
5. Filter unique distinct row records in excel 2007