Find the last entry in 3 rows!

[Paul Watkins]

Hi
Is there any way to use this formula to give the result from 3 columns
instead of 1 (A B & C) ?

=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))

Data (Text & Numbers) in the columns like this
A1 = Ice Cream 12 tubs
B2 = Lollipops packs of 5
C3 = Choc Ices box of 5


Thanks
Paul

Hi Paul
depends on the kind of values in column A
1. If you have only text values in column A try
=INDEX(Entry!A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(Entry!A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(Entry!A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))

Hi, Paul.

Jason's line should work just fine (it does for me).
Be sure to check that you have automatic calcuation
turned on (Tools->Options->Calculations) or hit F9.
(Note you'll get a zero if any row (like A34)is blank)

BTW if the values were entered 'across' the sheet,
say always in row 3, the formula should be changed
by substituting 3 for A.

HTH.
jeff

 

[Frank Kabel]

Hi Paul
what do you exactly want (a concatenqation of the last row or each last
cell per row)?

 

[Paul Watkins]

Hi Frank
I think i found a way round it.
What i've done is added a concatate in Column D which looks at A1, B1 & C1
and displays the 'filled' cell in that row. Then i've placed the formula
below into column E. This then displays a '0' because of the concatate
formula in D1. So to get around that i've added vb code under the sheet to
transfer the 'Text' value of E1 to F1. This then displays the correct value
of 'Ice Cream'.
A bit long winded, but it gives me the result i want.

Thanks again.

Paul