Friday, August 5, 2016

Excel Tips: Headers, Footers, Marcos (Updated)

1. Adding a File Path and Fileanme
If you need to show the current workbook's filename and path in a header or footer, you can use the following
Path: &[Path]
File Name: &[File]
Page: &[page]
Total pages: &[pages]
Name of worksheet: &[tab]

If you need to insert the file path and file name into a cell, try the following
If you want to strip out the brackets and the sheet name, you can use the following variation on the formula:

2. Show the "Ampersands &" in header or footer, you need to &&
e.g. Tom && Jerry
it will be shown as "Tom & Jerry"

3. Add auto "last modified date"
You need to have a macro as the following

Private Sub Workbook_SheetChange(ByVal _
  Sh As Object, ByVal Target As Excel.Range)
    ActiveSheet.PageSetup.CenterFooter = _
      "Worksheet Last Changed: " & _
      Format(Now, "mmmm d, yyyy hh:mm")
End Sub

Or the following as last saved date

Private Sub Workbook_BeforeSave(ByVal _
  SaveAsUI As Boolean, Cancel As Boolean)
    Dim sht
    For Each sht In Sheets
        sht.PageSetup.CenterFooter = _
          "Workbook Last Saved: " & _
          Format(Now, "mmmm d, yyyy hh:mm")

End Sub

With font and font size assigned

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sht
    For Each sht In Sheets
        sht.PageSetup.RightHeader = _
          "&""Calibri""&8" & _
          "Updated on: " & _
          Format(Now, "mmmm d, yyyy")
End Sub

The best way to accomplish the task above is to add a macro to the ThisWorkbook object that is triggered just before a workbook is saved.

For Windows, how to create a Macro

Wednesday, July 13, 2016

Missing Drop Down Arrows in Pull down Cell

In Windows,

"Missing arrows" is a fairly common problem, and usually they can be fixed by using the keyboard shortcut to show objects -- Ctrl + 6

Can fix the issue.

How about Office in Mac?

Wednesday, March 16, 2016

Where is the hash key

Where is the hash key from the Apple Keyboard connecting to iPad?

Ans: Alt + 3 (press alt and 3 key)

Wednesday, February 17, 2016

Useful Shortcuts in Mac OS X

  1. In Safari, go to top of the page or go to bottom of the page
    1. Shortcut: Command + "Up arrow" or Command + "Down arrow
  2. In Safari, shortcut to tabs
    1. Shortcut: Command + "number"
  3. Fine tuen the volume -- by pressing F11 or F12, the system will increase or decrease the volume level by one unit, if you want to have a fine adjustment, the following shortcut will take one quarter unit of volume adjustment.
    1. Shortcut: Option + Shift + F11 or F12
  4. Show the desktop 
    1. Command + F3
  5. Delete a word 
    1. shortcut: Option + Delete

How to show Chinese Calendar in Calendar app in Mac

View the lunar calendar

Choose Calendar > Preferences, click General, click the “Show alternate calendar” pop-up menu, then choose a lunar calendar.

Sunday, January 31, 2016

Excel Useful Formula #1

1. Find the repeated contents: =if(countif(A:A,A2)>1, "Repeated content", "")

2. Formula: =TRUNC((DAYS360(D1,today(), FALSE))/360, 0)

D1: is your target cell for input date of birth
today(): is the formula to return the date of today

3. Ranking: =rank(K2, K$2:K$56) ranking of 55 students results

4. Grading: =if(K2>=85, "A", If(K2>=74, "B", IF (K2>=60, "Pass", "Fail")))

5. Count with condition:

=countif(B2:B56, "80")

=countif(B2:B56, ">=80")

Friday, January 1, 2016

Sorting in OSX

In OSX, sorting and arranged by is different.

  • View > Arrange By > None (^⌘0)
  • Hold Option, View > Sort By > Name (^⌥⌘1)

Try the above commands, you will see the difference!