About locate last cell in the last column

B

bobocat

This question again
I tried to use =index(a:a, max(match({"zzzzzzzzzz",9.9999999E+307},A:A))) to
locate the last value in a column. It will be good if the column contain at
least one numberic value . If the whole column is text, it returns #N/A

I know that, if the whole column is text or number, I can use the other
formula. However, in this column, the data maybe solely text, maybe soley
number, maybe text or number. So what can I do?

any one can help me?

Since I want to put this formula to a defination of named range, I cannot
put the array formula to the defination.

Sorry to ask the same question again.

Bobocat
 
R

Ragdyer

This is a *non* array formula that will return the last value in the
assigned range,
whether it be text *or* numbers.

=LOOKUP(2,1/(1-ISBLANK(A1:A100)),A1:A100)
 
B

bobocat

I tried this before.
I can type this formula to the defination of name, however, when I use goto
feature, "Reference is not valid" returned
 
B

Bob Phillips

Try this to your name

=INDEX($A$1:$A$100,MATCH(LOOKUP(2,1/(1-ISBLANK($A$1:$A$100)),$A$1:$A$100),$A
$1:$A$100,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

Ragdyer

Hey Bob,

Since you made a suggestion, I assume you fathom exactly what bobcat is
trying to do.
Would you be so kind as to explain that to me.

All I get, is that he's trying to "go to" a named formula.

I'm sure there must be more to it then that!
 
B

Bob Phillips

Ragdyer said:
Hey Bob,

Since you made a suggestion, I assume you fathom exactly what bobcat is
trying to do.
Click to expand...

Now let's not over-egg it said:
Would you be so kind as to explain that to me.

All I get, is that he's trying to "go to" a named formula.
Click to expand...

That is exactly when I deduced, or at leats, goto the cell that results from
that named formula, and I was surprised at the problem he was getting. So I
tried it, and I got the same problem.

First I thought it might be due to relative ranges being screwed up when
called from another cell, so I made it absolute. Same problem.

I then thought maybe the CF was taking the cell value not the cell, so I
tried Match, which obviously failed as it just gave the row index. I then
stuck INDEX in the front, and it seemed to work okay.
 
Top