Column Reference
 

Hi,

On Row 3 - I have text from columns "B3:J3".

I am constanly adding and clrearing the rightmost cell, so that the last
column is J, P, K, ...etc.

Is there any way to create a function in Cell A1 so that it will give me the
letter of the rightmost column with text (whichever is nonempty). - (I am
using this with the indirect function to do a vlookup)

So if the text spans from column B to column AJ - then Cell A1 = AJ
If text spans to column K, Cell A1= K

Any help would be greatly appreciated.

One way:

=LEFT(ADDRESS(1,COUNTA(B3:IV3)+1,4,TRUE), 1+(COUNTA(B3:IV3)25))


In article ,
"Jeff" wrote:

Hi,

On Row 3 - I have text from columns "B3:J3".

I am constanly adding and clrearing the rightmost cell, so that the last
column is J, P, K, ...etc.

Is there any way to create a function in Cell A1 so that it will give me the
letter of the rightmost column with text (whichever is nonempty). - (I am
using this with the indirect function to do a vlookup)

So if the text spans from column B to column AJ - then Cell A1 = AJ
If text spans to column K, Cell A1= K

Any help would be greatly appreciated.

You could use
=match("zzzzzzzzzzzzzzzzz",1:1) to give the answer 4 if the last column was
D
but why not just use a defined name for the range instead so that it is self
adjusting
insertnamedefinename it myrange or whatever
in the refers to box use the offset function (look in help index for more
info)
=offset($a$2,0,0,counta($a:$a),counta(1:1)) or similar

--
Don Guillett
SalesAid Software

"Jeff" wrote in message
...
Hi,

On Row 3 - I have text from columns "B3:J3".

I am constanly adding and clrearing the rightmost cell, so that the last
column is J, P, K, ...etc.

Is there any way to create a function in Cell A1 so that it will give me
the
letter of the rightmost column with text (whichever is nonempty). - (I am
using this with the indirect function to do a vlookup)

So if the text spans from column B to column AJ - then Cell A1 = AJ
If text spans to column K, Cell A1= K

Any help would be greatly appreciated.