Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
Looking for a quick and easy way to revert my Column Index Number back to the
respective Column Letter... TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
Maybe you don't need to. If you're referring to a range by cell address,
ActiveSheet.Range("A3"), you can also do it by row and column index, ActiveSheet.Cells(3,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "DJS" wrote in message ... Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
Jon is probably on the mark but you could use this UDF
Function GetColLet(ColNumber As Integer) As String GetColLet = Left(Cells(1, ColNumber).Address(False, False), _ 1 - (ColNumber 26)) End Function =GetColLet(1) returns A Gord Dibben MS Excel MVP On Thu, 7 Dec 2006 13:52:00 -0800, DJS wrote: Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
=LEFT(ADDRESS(1,col_index_num,4),LEN(ADDRESS(1,col _index_num,4))-1)
Biff "DJS" wrote in message ... Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
On Thu, 7 Dec 2006 13:52:00 -0800, DJS wrote:
Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA With the column index number in A1: =LEFT(ADDRESS(1,A1,4),1+(A126)) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
Another one:
=SUBSTITUTE(ADDRESS(1,col_index_num,4),1,"") Biff "T. Valko" wrote in message ... =LEFT(ADDRESS(1,col_index_num,4),LEN(ADDRESS(1,col _index_num,4))-1) Biff "DJS" wrote in message ... Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
Thanks All.
I need the Column Letter as a reference for the end user. I got the function to work (from Gord), but i kept getting the following error if i try and use any of the others "Compile Error: Sub or Function Not Defined". Do i have to put the other samples into a function like Gord did? I tried: colLtr = Left(Address(1, colNum, 4), Len(Address(1, colNum, 4)) - 1) Thanks again. "Ron Rosenfeld" wrote: On Thu, 7 Dec 2006 13:52:00 -0800, DJS wrote: Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA With the column index number in A1: =LEFT(ADDRESS(1,A1,4),1+(A126)) --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
On Fri, 8 Dec 2006 07:05:01 -0800, DJS wrote:
Thanks All. I need the Column Letter as a reference for the end user. I got the function to work (from Gord), but i kept getting the following error if i try and use any of the others "Compile Error: Sub or Function Not Defined". Do i have to put the other samples into a function like Gord did? I tried: colLtr = Left(Address(1, colNum, 4), Len(Address(1, colNum, 4)) - 1) Thanks again. "Ron Rosenfeld" wrote: On Thu, 7 Dec 2006 13:52:00 -0800, DJS wrote: Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA With the column index number in A1: =LEFT(ADDRESS(1,A1,4),1+(A126)) --ron Mine is a simple worksheet function. You just enter it into some cell in the worksheet, with the column number in A1 (or whatever cell you choose to specify). Biff's recommendation is similar, but he chose to use a Name'd reference for the location of the column number. --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Translate Column Index Num to Letter
Thanks Ron, that explains it.
I am working in a macro. "Ron Rosenfeld" wrote: On Fri, 8 Dec 2006 07:05:01 -0800, DJS wrote: Thanks All. I need the Column Letter as a reference for the end user. I got the function to work (from Gord), but i kept getting the following error if i try and use any of the others "Compile Error: Sub or Function Not Defined". Do i have to put the other samples into a function like Gord did? I tried: colLtr = Left(Address(1, colNum, 4), Len(Address(1, colNum, 4)) - 1) Thanks again. "Ron Rosenfeld" wrote: On Thu, 7 Dec 2006 13:52:00 -0800, DJS wrote: Looking for a quick and easy way to revert my Column Index Number back to the respective Column Letter... TIA With the column index number in A1: =LEFT(ADDRESS(1,A1,4),1+(A126)) --ron Mine is a simple worksheet function. You just enter it into some cell in the worksheet, with the column number in A1 (or whatever cell you choose to specify). Biff's recommendation is similar, but he chose to use a Name'd reference for the location of the column number. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying forumla for vlook up but changing the column Index # | Excel Worksheet Functions | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
How to automatically number an index column | Excel Worksheet Functions | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions |