Returning the last value in a column

Posted by Razzie3 on January 15, 2002 3:02 PM

I hope I haven't exceeded my limit of questions. I just found this board and have questions which no other knowledgeable folks have been able to answer. I hope some of you MS Excel Gurus can help me out. I attempting to make a table in the message but I don't know how it will turn out when published.

A B C D F G
1 1000 900 700 1200
2 200 800 1000
3 1200 1000 500 250 750

Is there a forumla that will look left from Column G and return the value of the fist cell that contains a value? In the example above I would want column G to end up having G1,1200 G2,1000 G3,750
I came up with a pretty complex method containing multiple IF and AND functions but I hope this is a better way.

Posted by Barrie Davidson on January 15, 2002 3:06 PM

Try this formula in G1 and copy down (assumes your data could be in column A to F).

=INDIRECT("R"&ROW()&"C"&COUNT(A1:F1),0)

Regards,
BarrieBarrie Davidson

Posted by Razzie3 on January 15, 2002 3:12 PM

Thanks again. I have worked many, many HOURS without success (or came up with complex workrounds requiring many cells of output to end up with one value).

Sorry to bother you guys with what must seem like simple questions.

Posted by Aladin Akyurek on January 15, 2002 3:18 PM

The expected results that you mention given the sample data is a bit confusing. However, for a column (say, A) of numeric type, you can use outside of that column

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

Of course, it can also be used for a restricted range:

=INDEX($A$1:$A$100,MATCH(9.99999999999999E+307,$A$1:$A$100))

The same method also works for rows (say, 1:1) or for a range in a row of numeric type:

That's, you can use outside of row 1:

=INDEX(1:1,MATCH(9.99999999999999E+307,1:1))

And:

=INDEX($A$1:$F$1,MATCH(9.99999999999999E+307,$A$1:$F$1))

Aladin

===========