Numbers to Letters?

[PaulW]

Currently I use alot of things like
=CONCATENATE("A",MATCH(B2,A:A,0)+1)

This will return something like A6 when im searching for yesterdays date and
I want to know the cell reference where today's date should go. Although,
thats a simple example of the macro's I use that puts information in certain
cells depending on what stuff is in other place.

My Question is, that this is fine for me, but when it comes to a Horizontal
match instead of a vertical one the answer is still a number. This leaves me
having to dedicate over 200 lines to do a vlookup on this number, so 3 = C
and 28 = AB.

Is there anyway to automatically determine from a number which column it is?
So I can use it in concatenated matches. What im currently working on looks
as 1:1 for "Total" then minus's 1, so each time the total is moved right for
a new column my information will go in this column.

Cheers for your time!

[Richard Schollar]

Paul

Can I suggest you use the ADDRESS function instead of CONCATENATE to generate the address value? That way you can easily use column numbers.

=ADDRESS(row_num,column_num)

There are some optional arguments after column number too - check Excel help.

Hope this helps!

Richard

[Bob Phillips]

If you used

=ADDRESS(ROW(INDEX(A1:A4,MATCH(B2,A1:A4,0)+1)),COLUMN(A1:A4))

that is easily extended to

=ADDRESS(ROW(1:1),INDEX(1:1,MATCH(B2,1:1,0)+1))

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"PaulW" <[email protected]> wrote in message
news:[email protected]...
> Currently I use alot of things like
> =CONCATENATE("A",MATCH(B2,A:A,0)+1)
>
> This will return something like A6 when im searching for yesterdays date
and
> I want to know the cell reference where today's date should go. Although,
> thats a simple example of the macro's I use that puts information in
certain
> cells depending on what stuff is in other place.
>
> My Question is, that this is fine for me, but when it comes to a
Horizontal
> match instead of a vertical one the answer is still a number. This leaves
me
> having to dedicate over 200 lines to do a vlookup on this number, so 3 = C
> and 28 = AB.
>
> Is there anyway to automatically determine from a number which column it
is?
> So I can use it in concatenated matches. What im currently working on
looks
> as 1:1 for "Total" then minus's 1, so each time the total is moved right
for
> a new column my information will go in this column.
>
> Cheers for your time!