Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers -please help
Hi--
I have a spreadsheet where I have a column letter(s) entered in cell AM2. For example cell AM2 has the text "df" to represent column df. I am trying to write a formula that will allow me to access data in the next column after df. So I need to: 1) Convert the text in AM2 to a column number 2) Increase the column number by 1 3) Convert it back to a column letter ??I think 4) Be able to reference that new column and the current row What I have tried is: =INDIRECT("AM2") --- Returns "df" which it should =COLUMN(INDIRECT("AM2")) --- Returns 39, which is the column number for AP, not DF. How do I get this to give me the column number of DF? Once that works, I think I can increase the column number by 1 easily. Then how do I take all that and convert it back to a cell reference using the current row? Thanks in advance for any suggestions. Jason |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers -please help
Have a look in XL Help for the OFFSET function.
Pete On Mar 6, 4:03*pm, Jason Lang wrote: Hi-- I have a spreadsheet where I have a column letter(s) entered in cell AM2. *For example cell AM2 has the text "df" to represent column df. I am trying to write a formula that will allow me to access data in the next column after df. *So I need to: 1) Convert the text in AM2 to a column number 2) Increase the column number by 1 3) Convert it back to a column letter ??I think 4) Be able to reference that new column and the current row What I have tried is: =INDIRECT("AM2") * --- Returns "df" which it should =COLUMN(INDIRECT("AM2")) * --- Returns 39, which is the column number for AP, not DF. *How do I get this to give me the column number of DF? Once that works, I think I can increase the column number by 1 easily. Then how do I take all that and convert it back to a cell reference using the current row? Thanks in advance for any suggestions. Jason |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers -please help
Following on, this formula will correctly return the column number of
column DF, where "DF" is in cell AM2: =COLUMN(INDIRECT(AM2&"1")) INDIRECT needs a cell reference, so you need to tag on a row number. Perhaps with this you can go back to your idea of adding 1 and converting back to a column for use in another INDIRECT formula. Or, you could look at using OFFSET ... Hope this helps. Pete On Mar 6, 4:13*pm, Pete_UK wrote: Have a look in XL Help for the OFFSET *function. Pete On Mar 6, 4:03*pm, Jason Lang wrote: Hi-- I have a spreadsheet where I have a column letter(s) entered in cell AM2. *For example cell AM2 has the text "df" to represent column df. I am trying to write a formula that will allow me to access data in the next column after df. *So I need to: 1) Convert the text in AM2 to a column number 2) Increase the column number by 1 3) Convert it back to a column letter ??I think 4) Be able to reference that new column and the current row What I have tried is: =INDIRECT("AM2") * --- Returns "df" which it should =COLUMN(INDIRECT("AM2")) * --- Returns 39, which is the column number for AP, not DF. *How do I get this to give me the column number of DF? Once that works, I think I can increase the column number by 1 easily. Then how do I take all that and convert it back to a cell reference using the current row? Thanks in advance for any suggestions. Jason- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers - please help
Not sure how you are going to be using our suggestions, so this may or may
not be able to be adapted to what you need to do... =COLUMN(INDIRECT(AM2&"1")) You can concatenate any number in place of the "1". Rick "Jason Lang" wrote in message ... Hi-- I have a spreadsheet where I have a column letter(s) entered in cell AM2. For example cell AM2 has the text "df" to represent column df. I am trying to write a formula that will allow me to access data in the next column after df. So I need to: 1) Convert the text in AM2 to a column number 2) Increase the column number by 1 3) Convert it back to a column letter ??I think 4) Be able to reference that new column and the current row What I have tried is: =INDIRECT("AM2") --- Returns "df" which it should =COLUMN(INDIRECT("AM2")) --- Returns 39, which is the column number for AP, not DF. How do I get this to give me the column number of DF? Once that works, I think I can increase the column number by 1 easily. Then how do I take all that and convert it back to a cell reference using the current row? Thanks in advance for any suggestions. Jason |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers - please help
Hi Jason
=ADDRESS(ROW(AM2),COLUMN(INDIRECT(AM2&"1"))+1,1) Returns $DG$2 if AM2 contains df Change the value after the last comma to change whether your want absolutes for Column only, Row only or Relative =ADDRESS(ROW(AM2),COLUMN(INDIRECT(AM2&"1"))+1,4) returns DG2 -- Regards Roger Govier "Jason Lang" wrote in message ... Hi-- I have a spreadsheet where I have a column letter(s) entered in cell AM2. For example cell AM2 has the text "df" to represent column df. I am trying to write a formula that will allow me to access data in the next column after df. So I need to: 1) Convert the text in AM2 to a column number 2) Increase the column number by 1 3) Convert it back to a column letter ??I think 4) Be able to reference that new column and the current row What I have tried is: =INDIRECT("AM2") --- Returns "df" which it should =COLUMN(INDIRECT("AM2")) --- Returns 39, which is the column number for AP, not DF. How do I get this to give me the column number of DF? Once that works, I think I can increase the column number by 1 easily. Then how do I take all that and convert it back to a cell reference using the current row? Thanks in advance for any suggestions. Jason |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers - please help
Given that INDIRECT is a volatile function its usage, depending on how many
of them there are in your worksheet, could affect the speed of your recalculations. Hence, you might want to consider one of the following non-volatile formulas to calculate the column number for the referenced column letters in AM2 instead. If the contents of AM2 will only ever reference column letters from XL2003 or earlier, then use this formula.... =26*IF(LEN(AM2)=2,FIND(LEFT(AM2),"abcdefghi"),0)+F IND(RIGHT(AM2),"abcdefghijklmnopqrstuvwxyz") If the contents of AM2 will only ever reference column letters from XL2007 or later, then use this formula instead... =676*IF(LEN(AM2)=3,FIND(LEFT(AM2),"abcdefghijklmno pqrstuvwx"),0)+26*IF(LEN(AM2)1,FIND(MID(AM2,LEN(A M2)-1,1),"abcdefghijklmnopqrstuvwxyz"),0)+FIND(RIGHT(A M2),"abcdefghijklmnopqrstuvwxyz") Rick "Jason Lang" wrote in message ... Hi-- I have a spreadsheet where I have a column letter(s) entered in cell AM2. For example cell AM2 has the text "df" to represent column df. I am trying to write a formula that will allow me to access data in the next column after df. So I need to: 1) Convert the text in AM2 to a column number 2) Increase the column number by 1 3) Convert it back to a column letter ??I think 4) Be able to reference that new column and the current row What I have tried is: =INDIRECT("AM2") --- Returns "df" which it should =COLUMN(INDIRECT("AM2")) --- Returns 39, which is the column number for AP, not DF. How do I get this to give me the column number of DF? Once that works, I think I can increase the column number by 1 easily. Then how do I take all that and convert it back to a cell reference using the current row? Thanks in advance for any suggestions. Jason |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to cell reference and column letters to numbers -please help
Thanks all for the great suggestions. Works perfectly!
Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
coulmns with numbers instead of letters as the reference? | Excel Discussion (Misc queries) | |||
Converting letters to numbers | Excel Worksheet Functions | |||
converting letters to numbers | Excel Discussion (Misc queries) | |||
Converting column & row numbers to a cell reference | Excel Worksheet Functions | |||
How can I write in a text in a cell using numbers and the letters. | Excel Discussion (Misc queries) |