Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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. –  Kaonashi Aug 23 '09 at 12:24
add comment

3 Answers

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
add comment

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

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.