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!

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


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.excelforum.com/member.php...o&userid=34698
View this thread: http://www.excelforum.com/showthread...hreadid=545800

If you used

=ADDRESS(ROW(INDEX(A1:A4,MATCH(B2,A1:A4,0)+1)),COL UMN(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" wrote in message
...
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!