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

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!

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

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



"Babymech" wrote:

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!

How to reference Cells AA and forward with CHAR (and a bonusquest
 

Babymech wrote...
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.