1

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

Improve this question
2
  • 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
    Commented Aug 23, 2009 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
    Commented Aug 23, 2009 at 12:24

3 Answers 3

Reset to default
3

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",""))
Improve this answer
2
  • both of those formulas can be shortened to | =ADDRESS(ROW(),COLUMN()-1) | still long but a bit shorter
    – Xantec
    Commented Nov 2, 2010 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
    Commented Nov 2, 2010 at 18:26
2

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

Improve this answer
1

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

Improve this answer
1
  • 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
    Commented Nov 2, 2010 at 13:05

You must log in to answer this question.

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