'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.