Formula to return column letters

[DoubleZ]

In Excel 2007, I need to use the letters of the column that I am in in the
formula bar. However, I don't need a particular cell, just the letters.
Because of this, the letters do not change as I use Autofill down the row. I
am using over 500 columns, so I really need for Autofill to do this for me.
Basically, in the formula bar, I have "A" in part of my formula (when I am in
column A), but when I autofill, every column still has "A" rather than
updating. Is there a formula that returns the column letters of the current
position?

 

[John C]

This works in xl2003....
=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND("$",ADDRESS(ROW(),COLUMN(),2))-1)

 

[Rick Rothstein \(MVP - VB\)]

A little shorter (and 5 function calls less)...

=SUBSTITUTE(ADDRESS(1,COLUMN(),2),"$1","")

Rick

 

[Gord Dibben]

Columns are vertical, rows are horizontal.

You cannot copy a formula down one column and have the column referfence
change.

This formula entered in A2 and copied down 500 rows will give you the
contents of row 1

=INDEX($1:$1,ROWS($1:1))


Gord Dibben MS Excel MVP

 

[DoubleZ]

Thanks Rick and John C.

A little shorter (and 5 function calls less)...

=SUBSTITUTE(ADDRESS(1,COLUMN(),2),"$1","")

Rick