2 Questions (Concantenate and Returning Ranges)

Posted by Tim on January 04, 2002 1:21 PM

1). I need to concantenate a range from a series of numbers. So, I want to convert

RowStart = 1
ColumnStart = 3
RowEnd = 6
ColumnEnd = 6

to "C1:G6".

Is there a function that will give the letter part of the string without having to write a giant If/Then?

2. It appears that I cannot return a range, determined in a procedure, to a function. Does this sound correct?

Thanks,
Tim.

Posted by Aladin Akyurek on January 04, 2002 1:25 PM

=ADDRESS(1,3)&":"&ADDRESS(6,6)

Aladin

=========

Posted by Adam S. on January 04, 2002 1:32 PM

Sure

Say you have
RowStart,ColumnStart,RowEnd,ColumnEnd in A1:D1

with A2:D2 holding their values (1,3,6,6)

use (to return C1:G6)

=Address(B2,A2,4)&":"&Address(D2,C2,4)

or you could (to return $C$1:$G$6)

=Address(B2,A2)&":"&Address(D2,C2)

Hope that helps
Adam S.

Posted by IML on January 04, 2002 2:06 PM

If you just want to get the letter of address, you could use
=LEFT(ADDRESS(ROW(A1),COLUMN(A1),2),1+(COLUMN(A1)>26))
or
=SUBSTITUTE(ADDRESS(ROW(A1),COLUMN(A1),4),ROW(A1),"")

but I think you'd be better served by what was already offered here.

As to your second question, use the indirect function.

good luck.