How to reference Cells AA and forward with CHAR (and a bonus quest

[Babymech]

I am in a position where I want to reference cells with 11 columns in between
them, i.e. if I first reference Cell A4 I then want to reference Cell M4 and
then Cell Y4 and so on. The first cell would then be Cell =CHAR(65)&4
Obviously you can guess what the problem is, since character there is no CHAR
value for AA or AB - the closest I get is =CHAR(65)&CHAR(65). So how do I set
it up so that I can make a CHAR-reference to cell AA1 without using two CHAR
commands?

For simplicity's sake: =CHAR(65+12*B1)&1 where B1=3 returns lower case "e"
and 1 where I would want it to return AK and 1.

Second question, somewhat related: what am I doing wrong with
=INDIRECT("CHAR("&65&")")

or for that matter

=INDIRECT("CHAR(65)")

I want both of these to resolve as CHAR(65) - i.e. capital "A" - but instead
I get #REF!

I'm sure these are newbie questions, but my first browse through the forums
didn't find me the answers so I'm hoping someone will have the time to answer
them. Thanks!

 

[dhstein]

Here is a UDF (User Defined Function) which will return the column. Go to
Tools - Macros - Visual Basic Editor - and paste it in to a module. Then go
to File - Close and return to microsoft Excel. You can then use the =COL()
function. Also try something like =INDIRECT(CHAR(65)&1) - this will display
the contents of cell "A1" Let me know if I've confused you. Here is the UDF:

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(Column(AM1)) - this would return the value "AM"
' Can be called as col() which returns the column of the current cell
' Alternatively

Select Case Column

Case Is > 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0

Addr = Application.Caller.Address
Col = Split(Addr, "$")(1)
End Select
End Function

 

[Harlan Grove]

I am in a position where I want to reference cells with 11 columns
in between them, i.e. if I first reference Cell A4 I then want to
reference Cell M4 and then Cell Y4 and so on. . . .

....

So every 12th column?

For simplicity's sake: =CHAR(65+12*B1)&1 where B1=3 returns lower
case "e" and 1 where I would want it to return AK and 1.

You want the return value to be the text string AK1?

=ADDRESS(1,1+B1*12,4)

Second question, somewhat related: what am I doing wrong with
=INDIRECT("CHAR("&65&")")

or for that matter

=INDIRECT("CHAR(65)")

I want both of these to resolve as CHAR(65) - i.e. capital "A" -
but instead I get #REF!

INDIRECT returns range references. It doesn't evaluate expressions.
It's argument must be a VALID range address. Neither A nor CHAR(65)
are valid range addresses, so INDIRECT correctly returns a #REF! error.