Formula for cell reference in excel

[VLG711]

I am trying to construct a formula that that will locate and return the last entered amount in a column. The example would be

=TEXT(K & COUNTA(K:K),K7)

COUNTA all cells with text = 28
Value to return should be in K28

Any help is appreciated.

VLG711

 

[SkipVought]

Hi,

=INDEX($A:$A,COUNT($A:$A),1)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

Try:

=OFFSET(K1,COUNTA(K:K)-1,0)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

sorry...

=TEXT("K"&INDEX(K:K,COUNT(K:K),1),0)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

If you just want the last value in the column, regardless of whether or not you have any blanks in your data, then the following will both do it:-

=LOOKUP(9.99999999999999E+307,K:K)

=INDIRECT(&quot;K&quot;&SUMPRODUCT(MAX((ROW(K1:K1000))*(K1:K1000<>&quot;&quot)))

If you actually want the address, then the following should suffice:-

=&quot;K&quot;&SUMPRODUCT(MAX((ROW(K1:K1000))*(K1:K1000<>&quot;&quot))

Regards
Ken...................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------