Column() to return a letter instead of a number?
 

Can column() return a letter instead of a number? I am planning to use
it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

Hi
yes, it's possible, insert this into your formula:

CHAR(COLUMN()+64)

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

Awesome! worked like a charm... Thanks! :)


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

pleased I could help :-)
I hope the issue of columns greater than Z don't cause any problems...

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

oh, =INDIRECT(CHAR(COLUMN(E5)+64)&ROW()) worked, but what if I wanted to
get the cell from a different sheet but the same cell location? ie.
=INDIRECT(Sheet1.CHAR(COLUMN(E5)+64)&ROW()) -- but this didn't work, is
there something missing from the function?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

if column =AA?

so better

=LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2))

-------
Ioannis, Athens



"cKBoy" wrote:


Can column() return a letter instead of a number? I am planning to use
it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

It works only until column Z, if cKBoy needs two-letter column identifiers
(AA, AB, ...) as well, he should use

=LEFT(ADDRESS(1,COLUMN(),4,1),IF(CODE(MID(ADDRESS( 1,COLUMN(),4,1),2,1))57,2,1))

Regards,
Stefi

€žbroro183€� ezt Ã*rta:


Hi
yes, it's possible, insert this into your formula:

CHAR(COLUMN()+64)

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

It's interesting why obvious solutions don't come up sometimes!
Stefi


€žÎ“ΙΑÎ�Î�ΗΣ Χ.Î’.€� ezt Ã*rta:

if column =AA?

so better

=LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2))

-------
Ioannis, Athens



"cKBoy" wrote:


Can column() return a letter instead of a number? I am planning to use
it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

try
=INDIRECT("sheet1!"&LEFT(ADDRESS(1,COLUMN(sheet1!E 5),4),IF(COLUMN(sheet1!E5)<27,1,2))&ROW(sheet1!E5) )
----
Ioannis, Athens

"cKBoy" wrote:


oh, =INDIRECT(CHAR(COLUMN(E5)+64)&ROW()) worked, but what if I wanted to
get the cell from a different sheet but the same cell location? ie.
=INDIRECT(Sheet1.CHAR(COLUMN(E5)+64)&ROW()) -- but this didn't work, is
there something missing from the function?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

=INDIRECT(Sheet1.CHAR(COLUMN(E5)+64)&ROW())

Try putting it as:
=INDIRECT("'Sheet1'!"&CHAR(COLUMN(E5)+64)&ROW())
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Column() to return a letter instead of a number?
 

Hi,

CkBoy, to answer your question about the indirect function, I've put
together your attempt with Ioannis', try:

=INDIRECT("'sheet
2'!"&LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2 )&ROW()))

NB: The single apostrphes on each side of the sheet name mean that the
formula will work if the sheet name has spaces in it.
The "sheet 2" can be replaced with the appropriate sheet name or if you
have a list of sheet names (eg the first one is in cell A2 of the active
sheet) try:

=INDIRECT("'"&A2&"'!"&LEFT(ADDRESS(1,COLUMN(),4),I F(COLUMN()<27,1,2)&ROW()))


btw, Stefi/Ioannis, yes, you're right it is interesting why the obvious
doesn't come up straight away - Thanks for showing me something new :-)

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

Hi broro183,
I was working on this sort of problem only last night using the same
idea of CHAR(COLUMN() + 64). I fiddled around for ages to overcome the
problem of going beyond column Z and came up with
=IF(INT((COLUMN()-1)/26)<1,"",CHAR(64 + INT((COLUMN()-1)/26))) &
CHAR(64 + MOD(COLUMN() + 25,26) +1)
It works but is far too cryptic to actually remember. I later thought
that the ADDRESS function might offer a better solution and came up
with =LEFT(ADDRESS(ROW(),COLUMN(),4),LEN(ADDRESS(ROW(), COLUMN(),4))
-LEN(ROW())) which just extracts the column address characters from the
string returned by the ADDRESS function, which is more straight forward
than the previous formula.
Just thought you might be interested:-)

Ken Johnson

Column() to return a letter instead of a number?
 

Hi Ken,
Thanks, I really like seeing the other options which are available
"outside the square of" my knowledge :-)
You're right, it is cryptic & although I overlooked the use of
"Address", I'd never have even thought of the use of "Int".
I think Ioannis has beaten us both though - IMHO, the use of "If" in
LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2) is the tidiest approach
of all.

Cheers
Rob Brockett
NZ
always learning & the best way to learn is to experience...

Ken Johnson Wrote:
Hi broro183,
I was working on this sort of problem only last night using the same
idea of CHAR(COLUMN() + 64). I fiddled around for ages to overcome the
problem of going beyond column Z and came up with
=IF(INT((COLUMN()-1)/26)<1,"",CHAR(64 + INT((COLUMN()-1)/26))) &
CHAR(64 + MOD(COLUMN() + 25,26) +1)
It works but is far too cryptic to actually remember. I later thought
that the ADDRESS function might offer a better solution and came up
with =LEFT(ADDRESS(ROW(),COLUMN(),4),LEN(ADDRESS(ROW(), COLUMN(),4))
-LEN(ROW())) which just extracts the column address characters from
the
string returned by the ADDRESS function, which is more straight
forward
than the previous formula.
Just thought you might be interested:-)

Ken Johnson


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

Hi broro,
I didn't spot Stephi's reply until after replying myself. The Greek
through me off and his reply was hidden under the blue Show quoted
text. It's funny how our thinking sometimes takes us up the garden
path.
Ken Johnson

Column() to return a letter instead of a number?
 

Hi broro,
a funny thing happened up the garden path!
When the next version of Excel is released I believe it will have 16384
columns. If Microsoft sticks with the alphabetical column headings they
will go up to Column XFD.
Stephi's formula will not cope with the extra letter in the column
headings.
My formula, quite by accident I must admit, will still work!
Ken Johnson

Column() to return a letter instead of a number?
 

Awesome suggestions.. thanks a lot. I have tried
=INDIRECT("'Sheet'!"&CHAR(COLUMN()+64)&ROW()) and it works perfectly!
:) now to try the more complex version for my AA AB columns. Thanks
again guys... you have been very helpful indeed. :)


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512119

Column() to return a letter instead of a number?
 

=IF(INT(COLUMN()/26)=0,"",CHAR(INT((COLUMN()-1)/26)+96))&CHAR(IF(MOD(COLUMN(),26)=0,26,MOD(COLUMN( ),26))+96)

Change +96 to +64 for CAPS
Point the column() if you do not want the current row
(i.e. column($A1)

On Thu, 16 Feb 2006 21:15:19 -0600, cKBoy
wrote:


Awesome suggestions.. thanks a lot. I have tried
=INDIRECT("'Sheet'!"&CHAR(COLUMN()+64)&ROW()) and it works perfectly!
:) now to try the more complex version for my AA AB columns. Thanks
again guys... you have been very helpful indeed. :)