Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula converting number to column letter 26
I've always used A1 notation in my formulas as well as VBA. When I need to
select a range from anything higher than column Z, I generally use VBA to convert to a multi-letter column ID. I'm working in someone else's workbook, and want to avoid VBA/macros (and might as well learn something new). The existing formula is: =INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781") and now that the data has hit more than 26 columns, it is throwing a reference error. Is there a simple replacement to reference any column's alpha ID, even the 2-digit IDs? If not, what would be the appropriate way to replicate the above statement with R1C1 notation (I'm assuming I can use R1C1 in only 3 cells and leave the rest of the workbook in A1 notation, there are hundreds of formulas I don't want to change) Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula converting number to column letter 26
Hi Keith,
INDIRECT() has an optional 2nd argument which allows R1C1 reference style -- Kind regards, Niek Otten "KR" wrote in message ... I've always used A1 notation in my formulas as well as VBA. When I need to select a range from anything higher than column Z, I generally use VBA to convert to a multi-letter column ID. I'm working in someone else's workbook, and want to avoid VBA/macros (and might as well learn something new). The existing formula is: =INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781") and now that the data has hit more than 26 columns, it is throwing a reference error. Is there a simple replacement to reference any column's alpha ID, even the 2-digit IDs? If not, what would be the appropriate way to replicate the above statement with R1C1 notation (I'm assuming I can use R1C1 in only 3 cells and leave the rest of the workbook in A1 notation, there are hundreds of formulas I don't want to change) Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula converting number to column letter 26
Thank Niek!
I think I was having a brain-dead moment when I was looking for a syntax change; I just remembered that I could use an offset command and get anywhere I need to be.. :) Thanks, keith "Niek Otten" wrote in message ... Hi Keith, INDIRECT() has an optional 2nd argument which allows R1C1 reference style -- Kind regards, Niek Otten "KR" wrote in message ... I've always used A1 notation in my formulas as well as VBA. When I need to select a range from anything higher than column Z, I generally use VBA to convert to a multi-letter column ID. I'm working in someone else's workbook, and want to avoid VBA/macros (and might as well learn something new). The existing formula is: =INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781") and now that the data has hit more than 26 columns, it is throwing a reference error. Is there a simple replacement to reference any column's alpha ID, even the 2-digit IDs? If not, what would be the appropriate way to replicate the above statement with R1C1 notation (I'm assuming I can use R1C1 in only 3 cells and leave the rest of the workbook in A1 notation, there are hundreds of formulas I don't want to change) Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula converting number to column letter 26
Keith
From your formula I understand that InfoLists!D19 currently contains the numbers 0-25, to reference columns 1-26. With this philosophy, the following formula should work: =INDIRECT(("RawOut!" & IF(InfoLists!D19<26,CHAR(InfoLists!D19+1+64) & "1", CHAR(INT(InfoLists!D19/26)+64)&CHAR(MOD(InfoLists!D19,26)+1+64)&"781")) HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula converting number to column letter 26
Lol, this shows how a very complicated way of thinking can save you
seconds of work... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula converting number to column letter 26
=INDIRECT(ADDRESS(781,D19+1))
HTH -- AP "KR" a écrit dans le message de ... I've always used A1 notation in my formulas as well as VBA. When I need to select a range from anything higher than column Z, I generally use VBA to convert to a multi-letter column ID. I'm working in someone else's workbook, and want to avoid VBA/macros (and might as well learn something new). The existing formula is: =INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781") and now that the data has hit more than 26 columns, it is throwing a reference error. Is there a simple replacement to reference any column's alpha ID, even the 2-digit IDs? If not, what would be the appropriate way to replicate the above statement with R1C1 notation (I'm assuming I can use R1C1 in only 3 cells and leave the rest of the workbook in A1 notation, there are hundreds of formulas I don't want to change) Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Converting a number to a letter in Excel | Excel Worksheet Functions | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Get Column Letter from Column Number | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |