Learn something new everyday - why one blank cell causes a count in pivot tables (Excel)

Picked this up from Pivot Table Data Crunching Microsoft Excel 2010 (pg. 63):

If all cells in a column contain numeric data, Excel chooses to sum.  If just one cells is either blank or contains text, Excel chooses to count.

I have definitely encountered this before when building pivot tables, and just shrugged it off a quirk of Excel.  Interesting! Now I know why Excel Pivot Tables would default to a Count of Values instead of Sum.  Next time I'll ensure my base data doesn't contain any blanks. 

Any other pivot table tips out there waiting to be discovered?

Finding home - Synchronizing all sheets in a workbook [Exel VBA]

I often work in large workbooks of 50+ sheets, and I typically set every worksheet to cell A1 before I distribute the file to other bears.

Problem: Needless to say the last thing I want to do before I close out of my file is to manually sync each and every sheet.

Solution: Macro that goes through every sheet and sets the view to your activeview (e.g. cell L29 is the Top-Left cell visible in your worksheet) and sets the activecell on every worksheet to your current activecell (e.g. cell N51).

I use the ScrollRow and ScrollColumn properties to determine the Top-Left most visible cell, and the Goto method to set the activeview.

To prevent errors if there's a ChartSheet in the workbook, there's a custom function in the code below that traps this condition.

'---------------------------------------------------------------------------------------
' Procedure : Synchronize_Worksheets
' Author    : Business Bear
' Date      : 2/24/2012
' Purpose   : Synchronizes all visible worksheets to selected cell.  Note, does not
'             sync hidden worksheets and chart sheets.  If range of multiple cells is
'             selected at run time, syncs to activecell
'---------------------------------------------------------------------------------------
'
Sub Synchronize_Worksheets()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim sSetCell As String
    Dim wsStart As Worksheet
    Dim sShtName As String
    Dim sGotoCellAddy As String
    Dim lCounter As Long
    Dim lScrRow As Long
    Dim lScrCol As Long

    Application.ScreenUpdating = False
    
    'Prevent errors if a workbook's not open
    On Error GoTo End_Sub
    Set wb = ActiveWorkbook
    sShtName = ActiveSheet.Name
    On Error GoTo 0
    
    'Use custom function to check if sheet is Chart sheet
    'Prevents the code from adjusting chart sheet / throwing errors
    If IsChartSheet(sShtName) Then
        Exit Sub
    End If
    Set wsStart = wb.ActiveSheet
    
    'Find visible area of activewindow; this will align each worksheet
    lScrRow = ActiveWindow.ScrollRow
    lScrCol = ActiveWindow.ScrollColumn
    'Top-Left most cell in the visible range
    sGotoCellAddy = Cells(lScrRow, lScrCol).Address(False, False, xlA1)
        
    'Activecell from users selection
    sSetCell = ActiveCell.Address(Rowabsolute:=False, Columnabsolute:=False)
    
    lCounter = 0
    'Loop through each worksheet in the active workbook
    For Each ws In wb.Worksheets
        'Check is sheet is visible, if not skip; prevents errors
        If ws.Visible Then
            'Sets the visible range
            Application.Goto Reference:=ws.Range(sGotoCellAddy), Scroll:=True
            'Selects the cell the user wanted to sync
            ws.Range(sSetCell).Select
            'For the dialog box at end of code; counts sheets synced
            lCounter = lCounter + 1
        End If
    Next ws
    wsStart.Select
    'Display dialog box
    MsgBox Prompt:=lCounter & " worksheets syncronized to cell " & sSetCell, _
        Title:="Process Complete"

End_Sub:
    Application.ScreenUpdating = True

End Sub

'---------------------------------------------------------------------------------------
' Procedure : IsChartSheet
' Author    : Business Bear
' Date      : 2/24/2012
' Purpose   : Boolean; Tests if sheet is a chart sheet, returns true if true
'---------------------------------------------------------------------------------------
'
Public Function IsChartSheet(ChtName As String) As Boolean
    Dim chrtTest As Chart

    On Error Resume Next
    Set chrtTest = Charts(ChtName)
    On Error GoTo 0
    IsChartSheet = IIf(Not chrtTest Is Nothing, True, False)
End Function

Fun with Mnemonics...$ is for...

A little birdie told me the following today:

If you have trouble remembering what the $ symbol means in the address bar (e.g. $A$1), just remember that $ is for stay.

That is, the $ symbol indicates an absolute reference and the reference won't move when copied.