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 |
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 |
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 - |
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 |
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 |
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 |
Converting text to cell reference and column letters to numbers -please help
Thanks all for the great suggestions. Works perfectly!
Jason |
All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com