Breaking News: Grepper is joining You.com. Read the official announcement!

Excel formula to reference CELL TO THE LEFT

Excel Hero answered on April 2, 2020 Popularity 6/10 Helpfulness 6/10

answer Excel formula to reference CELL TO THE LEFT

Excel formula to reference CELL TO THE LEFT

Comment

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

Popularity 6/10 Helpfulness 6/10 Language vb

Source: academy.excelhero.com

Tags: cell excel-formula reference vba

Link to this answer
Share Copy Link

Contributed on Apr 02 2020

Excel Hero

0 Answers Avg Quality 2/10

Excel formula to reference CELL TO THE LEFT

Contents

More Related Answers

Excel formula to reference CELL TO THE LEFT

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.