Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In excel, if I want to perform a function with the column to the left, how do I go about this?

share|improve this question

migrated from stackoverflow.com Nov 2 '10 at 11:35

This question came from our site for professional and enthusiast programmers.

    
What function do you want to perform? Do you want to create a new column of numbers that uses the "column to the left" or are you trying to condense the entire column to a single number (e.g., a sum or average)? We'll need a lot more information to be able to offer a useful answer. – VoteyDisciple Aug 23 '09 at 11:48
    
I just want to be able to select a different column which is independent of the number, so instead of selecting A1, it selects the cell to the left of the one which is running a function. – Tanami Aug 23 '09 at 12:24
up vote 2 down vote accepted

Here's how to reference the cell in the column to the left in a worksheet function:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))

there are probably other ways to do it, but I haven't found a good, terse way to self-reference an Excel cell.

To reference the entire column to the left in a worksheet function:

=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","") & ":" &  
   SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1",""))
share|improve this answer
    
both of those formulas can be shortened to | =ADDRESS(ROW(),COLUMN()-1) | still long but a bit shorter – Xantec Nov 2 '10 at 13:12
    
@Xantec, that just gives you the text address like '$A$1', he's looking for the value in that cell. I'm glad you had me revisit this because I see that the INDEX function isn't needed, so I'll edit my answer. – Lance Roberts Nov 2 '10 at 18:26

If you are in cell H1, and you want to sum column G, type =SUM(G:G).

share|improve this answer

Since this is a programming question site, I assume you mean in VBA.

The ActiveCell is the currently marked cell (or range), ActiveCell.Column will give you the column number (leftmost column for a range if memory serves me correctly) that you can use.

Simply subtract one and use it (assuming it's not 1 already, of course). If you want the actual column letter (you don't need it for calculations but may want it anyway), use:

Function GetCol (ByVal rng As Range)
    If rng.Column < 27 Then
        GetCol = Chr (64 + rng.Column)
    Else
        GetCol = _
            Chr (64 + (rng.Column - 1) \ 26) + _
            Chr (65 + (rng.Column - 1) Mod 26))
    End If
End Function

along with:

Dim colStr as String
colStr = GetCol (ActiveCell)
MsgBox (colStr)

If this isn't a VBA question, it doesn't really belong here (although I see by one of your comments that you're talking about running a function, so I guess you did mean in VBA after all).

share|improve this answer
    
or, if all you want is the address of the cell to the left then this code could be a lot simpler. | GetCol = cells(0,rng.column-1).address | keep in mind that this will return an error if it is run with a cell in column A – Xantec Nov 2 '10 at 13:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .