Converting text to cell reference and column letters to numbers -please help

[Jason Lang]

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

 

[Pete_UK]

Have a look in XL Help for the OFFSET function.

Pete

 

[Pete_UK]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Roger Govier]

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

 

[Rick Rothstein \(MVP - VB\)]

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)+FIND(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),"abcdefghijklmnopqrstuvwx"),0)+26*IF(LEN(AM2)>1,FIND(MID(AM2,LEN(AM2)-1,1),"abcdefghijklmnopqrstuvwxyz"),0)+FIND(RIGHT(AM2),"abcdefghijklmnopqrstuvwxyz")

Rick

 

[Jason]

Thanks all for the great suggestions. Works perfectly!

Jason