excel formula cell to left code example

Example: Excel formula to reference CELL TO THE LEFT

'Don't be tempted to use OFFSET() or even worse, INDIRECT() to 
'create a formula that always points at the cell to the left. 
'These are volatile functions that can weigh heavily on workbook
'performance. Fortunately, they are not needed at all.

'Instead, use a Named Range with Global Relative Referencing.
  
1.) To create the Name, first select cell B1.
  
2.) Open the Name Manager (Ctrl-F3 when a worksheet is selected).

3.) Click the New button and enter the name: Left1  
  
4.) In the Refers To field enter: =!A1
    
5.) Click OK and then Close
    
'-------------------------------------------------------------------    

'Now on any cell in the workbook (except in column A) you
'can enter the following formula: 

=Left1
    
'And the formula will reference the cell directly to the left.
    
'-------------------------------------------------------------------    

'Notice that the formula in the Refers To field in the Name Manager 
'does NOT have any ($) symbols and NO sheet name. Notice also that
'there is a (!) symbol before the cell reference. This combination 
'is what creates the global relative reference.