Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Bar blocks column header letters so I can't paste | New Users to Excel | |||
Formula bar obscures top of column letters | Excel Discussion (Misc queries) | |||
formula for column letters | Excel Discussion (Misc queries) | |||
How do change a column of data in capitol letters to small letters | Excel Discussion (Misc queries) | |||
Return column letters to spreadsheet. | Charts and Charting in Excel |