Jan 23, 2007 (T)

• Different types of cell references
  • entire columns and entire rows
    • entire column - e.g. A:A is every cell in column A
    • multiple columns - e.g. A:C is every cell from columns A,B and C
    • entire row - e.g. 1:1 is every cell in row 1, i.e. A1,B1,C1,....Z1,AA1,AB1,AC1...AZ1,BA1,BB1,...IV1
    • multiple rows - e.g. 2:4 is every cell in rows 2,3 and 4, i.e. A2 ... IV2, A3 ... IV3, A4 ... IV4
  • Cell on other worksheets
    • sheetname!cell reference. Examples:
      • goodStocks!a1 - cell a1 on the worksheet named goodStocks
      • goodStocks!c:e - all cells in columns c,d and e on worksheet goodStocks
    • If the sheetname has a space in it (e.g. good stocks instead of goodStocks) then you must surround the sheet name with apostrophes. Apostrophes in "computereeze" are sometimes referred to as "single quotes" (you should know this term). Example:
      • 'good stocks'!a1 - cell a1 on the worksheet named good stocks (with a space in the name)
  • 3d cell references
    • Example: Assume that there is a workbook that contains 12 worksheets (i.e. tabs). Each worksheet contains the data for a single month. The names of the worksheets are the names of the months (i.e. jan, feb, mar, etc) AND the sheets are arranged in the order of the months.
      • jan:dec!a1 - this includes the cell a1 from each one of the worksheets. This is shorthand for : jan!a1,feb!a1,mar!a1,apr!a1,may!a1,jun!a1,jul!a1,aug!a1,sep!a1,oct!a1,nov!a1,dec!a1
        
        
      • jan:mar!2:2 - this includes all the cells from row 2 of the worksheets jan, feb and mar. This is shorthand for: jan!2:2, feb!2:2, mar!2:2
  • Absolute cell references (see exelFormulas powerpoint presentation on yrosenthal.com)
    • 4 types of cell references:
      • B1
      • $B1
      • B$1
      • $B$1