Formula to return column letters
 

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?

Formula to return column letters
 

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


--
John C


"DoubleZ" wrote:

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?

Formula to return column letters
 

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

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

Rick


"John C" <johnc@stateofdenial wrote in message
...
This works in xl2003....
=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND("$",ADDRESS(R OW(),COLUMN(),2))-1)


--
John C


"DoubleZ" wrote:

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?

Formula to return column letters
 

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




On Tue, 22 Jul 2008 11:21:09 -0700, DoubleZ
wrote:

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?

Formula to return column letters
 

Thanks Rick and John C.

"Rick Rothstein (MVP - VB)" wrote:

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

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

Rick


"John C" <johnc@stateofdenial wrote in message
...
This works in xl2003....
=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND("$",ADDRESS(R OW(),COLUMN(),2))-1)


--
John C


"DoubleZ" wrote:

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?